Dynamic sum column in SUMIFS

0
votes

I have month columns and criteria for rows (Division, measure) and need a way to sum all divisions for a given measure and only return for the reporting month.

INDEX(MATCH) does not work because I need it to sum all Dec-18 absence values, but there are other measures in the columns as well.

My current iteration (array):

=SUM(OFFSET(D1:D53,,MATCH(Y3,$D$2:$P$2,0)))

But I can't get this to change the summing column based on the month selected.

My last guess is that I need to swap division and month (so division column headers, month rows), but I'd rather not if I'm missing something obvious.

Example:

Department  |      Measure        | Nov-18     |    Dec-18
Sales       |      Absence Hours  | 3.5        |    4.6
Manu        |      Absence Hours  | 6.2        |    1.7
Sales       |      Hours worked   | 1000       |    976
2
excelexcel-formulasumifs

2 Answers

0
votes

An alternative solution that does not use array like calculation nor volatile functions.

=SUMIF($B$2:$B$4,$F2,INDEX($C$2:$D$4,0,MATCH(G$1,$C$1:$D$1,0)))

In the event that either your criteria or date is not found an error will be displayed. You can deal with this by wrapping the whole thing in an IFERROR function and then choose your own error message, display blank or return 0.

=IFERROR(SUMIF($B$2:$B$4,$F2,INDEX($C$2:$D$4,0,MATCH(G$1,$C$1:$D$1,0))),"NOT FOUND")

POC

Also, if you wish to further breakdown your results but "department" and by "measure", then you could us SUMIFS which allows you to set multiple criteria instead of 1

0
votes

Maybe something like this would work?

=SUMPRODUCT((B3:B5=G3)*OFFSET(B3:B5;0;MATCH(G2;C2:D2;0)))

excel