SUMIFS multiple criteria, the CRITERIA (not the CRITERIA RANGE) is a range and it has blank cells

[GABxls]

Hello everyone,

I am trying to use the SUMIFS function, but my goal is to make the CRITERIA (not the Criteria Range) dynamic. I mean, I want to be able to add new values to "Event" and "Mode"(image below) without updating the SUMIFs.
In the example below, I want to calculate the TOTAL when the Event (column B) is equal to the "Event List" (column F) and the Mode (column C) is equal to "Modes List" (column G), but I want to write a function that will automatically handle new members to the "Events List" and "Modes List".

SUMIFS does not return the correct result when I add the cells F4:10 and G4:G10 as the criterias (the criterias for which columns B and C wil be evaluated).

 

[Fluff]

What formula did you use & in what way didn't it work?

 

[Special-K99]

Just make the end of the range bigger than the amount of data you will enter, say 1000, e.g.

=SUMIFS(D$3:D$1000,B$3:B$1000,F3,C$3:C$1000,G3)

 

[GABxls]

What formula did you use & in what way didn't it work?

I used the function below :
=SUM(SUMIFS(D4:D16;B4:B16;F4:F10;C4:C16;G4:G10))
But given that I have blank values in the range F4:F10 and range G4:G10, it does not return the correct sum.

 

[Fluff]

Thanks for that, when using two arrays one must be vertical & the other horizontal. Try

=SUM(SUMIFS(D4:D16;B4:B16;F4:F10;C4:C16;torow(G4:G10;1)))

 

[GABxls]

He

Just make the end of the range bigger than the amount of data you will enter, say 1000, e.g.

=SUMIFS(D$3:D$1000,B$3:B$1000,F3,C$3:C$1000,G3)

Thanks for that, when using two arrays one must be vertical & the other horizontal. Try

=SUM(SUMIFS(D4:D16;B4:B16;F4:F10;C4:C16;torow(G4:G10;1)))

YES !!! Thank you ! This new formula gives me the chances to make the criteria column (F and G) dynamic. Thank you so much !

 

[Fluff]

You're welcome & thanks for the feedback.