Sumif/sumifs/indirect

[smccarthy95]

Hello,

I've attached (I think) an example of what my work looks like. Each tab represents a different location, each color a different team. I'm trying to use the sumif(indirect) formulas to find the totals on the overview tab. Teams review/change qty often and the number of sn may increase/decrease. I don't know if I should be using SUMIF, SUMIFS, or INDIRECT formula. Each time I have tried using these I get errors.

Thank you for your assistance in this, have a great day.

[JohnTopley]

Create 3 named ranges (Sheets1-Sheets3) with list of tabs for Yellow, Blue and All

in B3

=SUMPRODUCT(SUMIF(INDIRECT("'" &Sheets1 &"'!A2:A100"),$A3,INDIRECT("'" &Sheets1 &"'!C2:C100")))

Copy down

Copy across and change Sheets1 to Sheets2 and Sheets3 respectively

[Estevaoba]

I assume the first three tabs after TOTALS tab refer to the Blue team.
If the number of tabs is variable for each team, you'll have to manually adjust any formulae you come up with.
I suggest that you keep one tab for each team, adding rows for new locations, instead of adding tabs.

[Estevaoba]

Create 3 named ranges (Sheets1-Sheets3) with list of tabs for Yellow, Blue and All

in B3

=SUMPRODUCT(SUMIF(INDIRECT("'" &Sheets1 &"'!A2:A100"),$A3,INDIRECT("'" &Sheets1 &"'!C2:C100")))

Superb, John! Congrats!