SUMIFS Statement with multiple OR functions

[DanielPratt]

Hey all,

First time poster and hoping to get a bit of help. I've got a set of data that I need to sum up with 2 conditions however one of the conditions has got multiple (roughly 70) or statements.

I can get this working with the below function:

=SUM(SUMIFS('QLD Data'!H:H,'QLD Data'!B:B,Summary!A3,'QLD Data'!D:D,{9010101,9010102}))

However this is only using 2 of the 70 conditions in the OR statement. If I've got a list of the conditions in another column can i use this data rather than rewriting all 70 conditions in the formula? These conditions will be changing, hence why they are listed in another section of the spreadsheet so they can be easy removed and added.

I've tried the below formula however have had no luck getting this to work... (this returned 0)

=SUM(SUMIFS('QLD Data'!H:H,'QLD Data'!B:B,Summary!A3,'QLD Data'!D:D,'Condition List'!B:B))

If I change the above formula to just select a specific condition this also works however I don't want to be repeating the below 70 times to get the total:

=SUMIFS('QLD Data'!H:H,'QLD Data'!B:B,Summary!A3,'QLD Data'!D:D,'Condition List'!B3)

Any help is muchly appreciated!

 

[FDibbins]

Have you considered using a helper column to ID the cells to include? It should be much simpler to use something like MATCH to text the codes against a list, and then, in the SUMIFS(), include only those rows/cells that return TRUE?

 

[Peter_SSs]

Welcome to the MrExcel board!

I would try to avoid whole column references.
Try changing to SUMPRODUCT and see if this works for you. Just make sure the ranges are larger than you are ever likely to need.
I've guessed 1000 rows on 'QLD Data' and 200 rows on 'Condition List'

=SUMPRODUCT(--('QLD Data'!B1:B1000=Summary!A3),--ISNUMBER(MATCH('QLD Data'!D1:D1000,'Condition List'!B1:B200,0)),'QLD Data'!H1:H1000)