Indirect Sumifs with Or Criteria

I didn't download your file.

Show us the formula you tried that didn't work.

You can accomplish OR logic within a SUMIFS function, but the criteria has to be explicitly declared.

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheet&"'!E:E"),INDIRECT("'"&Sheet&"'!B:B"),{"SUB.2","SUB.3"}))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Originally Posted by daffodil11

You can accomplish OR logic within a SUMIFS function, but the criteria has to be explicitly declared.

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheet&"'!E:E"),INDIRECT("'"&Sheet&"'!B:B"),{"SUB.2","SUB.3"}))

You can put the criteria in a horizontal range of cells.

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!B:B"),B2:C2,INDIRECT("'"&Sheets&"'!E:E")))

Woah, changing the workbook! That's against the rules!

Thank you for your help.

Daffodill, the formula you gave me worked but I need the criteria to be cell reference because the file will become a template and need to be changed per project and there could be up to 20 assets. Also, I don't believe the formula was a true array as it worked without using CTR+SHIFT+ENTER.

Tony, changing the criteria to a horizontal may be an option but it will require changing the layout of the spreadsheet. Can you explain why it works horizontal and not vertical? It will definitely be useful knowledge in the future.
Thanks,

Originally Posted by cloechl

Tony, changing the criteria to a horizontal may be an option but it will require changing the layout of the spreadsheet. Can you explain why it works horizontal and not vertical? It will definitely be useful knowledge in the future.

That's just how it works. A range of criteria has be in the opposite direction of the referenced range.

If you don't want to rearrange the criteria cells then you can use this array entered** version:

=SUM(SUMIFS(INDIRECT("'"&Sheet&"'!E:E"),INDIRECT("'"&Sheet&"'!B:B"),TRANSPOSE(B25:B26)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Also, since there is just a single criteria range you can simply use SUMIF although SUMIFS will still work.

Yes, plus one formula is calculating across multiple sheets while the other formula is calculating from a single sheet.