SOLVED

# Summing an area with one criteria on multiple rows

Copper Contributor

# Summing an area with one criteria on multiple rows

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 F G H I J Sum 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 1 2500 This is the result I am getting
7 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Summing an area with one criteria on multiple rows

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

See attached.

# Re: Summing an area with one criteria on multiple rows

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

# Re: Summing an area with one criteria on multiple rows

(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.

# Re: Summing an area with one criteria on multiple rows

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?

# Re: Summing an area with one criteria on multiple rows

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

An alternative could be SUMPRODUCT.

# Re: Summing an area with one criteria on multiple rows

@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.

# Re: Summing an area with one criteria on multiple rows

And Python in Excel for the collection

``````dfc = xl("A1:A16", 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

# Re: Summing an area with one criteria on multiple rows

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

See attached.