SOLVED

Summing an area with one criteria on multiple rows

Copper Contributor

I have a spreadsheet and trying to sum all of the rows and columns that match the criteria in the left column.  I am looking for the sum of Columns F, G, H, I, J that match "1".  What function can help me with this?  Thanks in advance

 

Criteria FGHIJSum I am looking for
1            500           100           200           600           700 
1            500           100           200           600           700 
1            500           100           200           600           700 
1            500           100           200           600           700 
1            500           100           200           600           700              10,500
2            500           100           200           600           700 
2            500           100           200           600           700 
2            500           100           200           600           700 
2            500           100           200           600           700 
2            500           100           200           600           700              10,500
3            500           100           200           600           700 
3            500           100           200           600           700                 4,200
4            500           100           200           600           700 
4            500           100           200           600           700 
4            500           100           200           600           700                 6,300
        
        
12500This is the result I am getting   
7 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@SharonMc5 

=SUM(MMULT(TRANSPOSE(F2:J16),--(A2:A16=A19)))

 

See attached.

 

Thank you so much! I have to admit I don't understand the equation. What is the purpose of the two hyphens?

@SharonMc5 

(A2:A16=A19) returns a series of TRUE/FALSE values: TRUE for each cell in A2:A16 that equals A19, FALSE for all other cells in A2:A16.

The - in -(A2:A16=A19) is a minus sign. It forces Excel to convert the result to numbers: TRUE is treated as 1 and FALSE as 0, so you now have a series of -1 / 0 values.

The second minus sign converts the -1 values to +1. So --(A2:A16=A19) is an array of 1 / 0 values and those can be used in MMULT.

@Hans Vogelaar 

Thank you for this explanation. I realized that the equation is returning a #value! in my spreadsheet. The only thing I can think is happening is that my spreadsheet totals are equations not actual keyed values. Is there a way to sum up the totals in the columns?

@SharonMc5 

=SUMPRODUCT((A2:A16=A19)*F2:J16)

 

An alternative could be SUMPRODUCT.

@SharonMc5 

@Riny_van_Eekelen 's solution is succinct and effective.

As a 365 user, I chose to take the solution as step further.

= LET(
    SumIfλ,  LAMBDA(v, SUM(FILTER(array, criterion=v))),
    critVal, UNIQUE(criterion),
    totals,  MAP(critVal, SumIfλ),
    HSTACK(critVal, totals)
  )

The main disadvantage is that it is harder to understand and is longer.  However, it identifies distinct criterial without user intervention and then generates totals for each criterion.  The criteria are stacked along with the relevant totals, so there is more automation.

image.png

@Peter Bartholomew 

And Python in Excel for the collection

dfc = xl("A1:A16", headers=True)
dfd = xl("F1:J16", headers=True)
dfc.assign(Total = dfd.sum(axis =1)) \
   .groupby("Criterion", as_index=False).sum()
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@SharonMc5 

=SUM(MMULT(TRANSPOSE(F2:J16),--(A2:A16=A19)))

 

See attached.

 

View solution in original post