Forum Discussion
Solved
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 he...
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.
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()