Summing across many sheets with criteria

0
votes

I'm using this formula to sum across many sheets, which works fine:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Table1[List1]&"'!$F$6:F$9"),INDIRECT("'"&Table1[List1]&"'!A$6:A$9"),$A8))

However, I need to add another criteria to the sum. I need it to sum only if the sheet contains a specific word in cell A1. I have 150 sheets and each contains either XX or YY in A1.

So in the end I would have two formulas: one that sums if A1 contains XX, and one that sums if A1 contains YY.

Tried tweaking the formula but keep getting errors

1
excelexcel-formula

1 Answers

0
votes

Try...

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Table1[List1]&"'!A1"),"xx"),SUMIFS(INDIRECT("'"&Table1[List1]&"'!$F$6:F$9"),INDIRECT("'"&Table1[List1]&"'!A$6:A$9"),$A8))

Hope this helps!