SUMIFS Sum Range Issue!

[thelostscott]

Hi all,

I'm trying to do a SUMIFS formula with a variable "sum_range".

The formula works when it reads:

=SUMIFS('Workings'!$M$5:$M$216,'Workings'!$F$5:$F$216,CONCATENATE($F$25,"/",$G26))

Then I need to add another criteria to the end of the formula where $I$24 is a changing date:

'Workings'!$G$4:$S$4,$I$24

But I need the "sum_range" to be variable dependant on the new criteria. Kind of like how an INDEX-MATCH formula works but to sum the column not just provide the first instance.

Is this possible without Pivot Tables?

 

[Jonmo1]

You can put an index match in the sumrange part of the sumifs..
But we'll need more specific information about how to determine which column to sum..

Example

=SUMIFS(INDEX('Workings'!$M$5:$Z$216,0,MATCH($I$24,$M$4:$Z$4,0)),'Workings'!$F$5:$F$216,CONCATENATE($F$25,"/",$G26))

That would make it sum a column between M and Z, based on the column that the value in I24 is found in Row 4 between M and Z.


Hope that helps.

 

[thelostscott]

Cheers for that, works a treat!