SUMIF or Index/Match to Sum values in different rows.

Question

Hello, I'm trying to find a formula that will allow me to sum values in a list based on criteria in a column. For example in the sample attachment, I'd like to sum all the "Water/Sewer" values in Column I based on criteria in Column B (Peach). Meaning sum all amounts in Column I for description "Water/Sewer" in Column H for only Unit Type "Peach" found in Column B. As you can see "Water/Sewer" does not line up with the unit type label and does not always appear a set amount of rows down making it difficult to use SUMIF with offset.

Was thinking about using index/match to make a new column with the amounts but sometimes the label "Water/Sewer" doesn't appear for some units so it will mistakenly take it from the next unit down. Any ideas are appreciated, let me know if the explanation isn't clear. Thank you.

Answer 1

Hi,

Enter this formula in cell M2

=SUM((SCAN(0,$B$2:$B$81,LAMBDA(a,i,IF(i="",a,i)))=K2)*($H$2:$H$81=L2)*($I$2:$I$81))

Hope this helps.

Answer 2

Hello,

If you want to have all the Water / Sewer values ​​for each Peach group (column B), then you can use the following formula (for orientation, see the printscreen above given in cell L2):

=CHOOSECOLS(FILTER(A2:I81, (LOOKUP(ROW(B2:B81), ROW(B2:B81)/(B2:B81 <> ""), B2:B81) = K2) * (H2:H81 = "Water / Sewer")), {8,9})

If you only want the total Water / Sewer for each Peach group (column B), then you can use the following formula:

=SUM(CHOOSECOLS(FILTER(A2:I81, (LOOKUP(ROW(B2:B81), ROW(B2:B81)/(B2:B81 <> ""), B2:B81) = K2) * (H2:H81 = "Water / Sewer")), 9))

I have placed the formula for the total result in cell M8.

In cell K2 I have set Type. If you want you can change Peach to Apple in this cell and you will get results for Apple and Water / Sewer.

HTH

Answer 3

Hi Al G.960,

Welcome to Microsoft Community.

If you are willing to create a new auxiliary column, consider entering this formula in L2 and filling it down. It will find the value in column I where column B is "Peach" and column H equals "Water/Sewer" for the corresponding group:

=IF(AND(H2="Water/Sewer", COUNTIF($B$1:B1, "Peach")>0), I2, "")

Then, you can simply use the SUM function to get the total.

=SUM(L2:L40)

If you want to obtain the result in one go, you need to convert the results of the auxiliary column into a dynamic array and then use the SUMPRODUCT function to sum the array:

=SUMPRODUCT(IF((H2:H40="Water/Sewer") *(MMULT(--(TRANSPOSE(ROW(B$1:B$40)) < ROW(H2:H40)-1),--(B$1:B$40="Peach")) > 0),I2:I40,""))

Hope this will help. If you have any other questions or want to share more context, please let me know in your reply!

Best Regards,

Thomas C - MSFT | Microsoft Community Support Specialist

Answer 4

Hi there

I see two possible solutions

1. Power Query
2. A formula solution with a helper column as you mentioned

Here is the second solution

1. Insert a new column, Type-2 as shown in the picture and enter the formula =LOOKUP(2,1/($B$1:B2<>""),$B$1:B2) and copy/drag it down.

2. Then you can use a formula like the following

=SUMIFS($I:$I,$H:$H,"Water/Sewer",$C:$C,"Peach")

Change the ranges in the formulas accordingly, you may also use cell ranges instead of hard typing the criteria parameters in the SUMIFS formula.

I hope this gives a solution to your problem

Regards

Jeovany

Answer 5

Hi,

Share the download link of the MS Excel file.