Excel SUMIF across Multiple tabs

Question

I have a workbook with multiple tabs with information that I'd like to aggregate about certain products.

The formula I have at the moment looks like this:

=SUMIF($Z:$Z,"Dogma",AA:AA)
=SUMIF($Z:$Z,"Dogma",AB:AB)
=SUMIF($Z:$Z,"Dogma",AC:AC)

etc.

I tried doing something like

=SUMIF('Austria:Portugal'!$Z:$Z,"Dogma",'Austria:Portugal'!AA:AA)

but that gave a #VALUE error.

Is it possible to convert my SUMIF to look across all tabs? I'm hoping to avoid replicating the table across all tabs and doing a SUM on the aggregate tab?

Many thanks.

Solution

Put the name of the desired sheets in a list and name that range; I named mine sheet:

Then you can use the following formula:

=SUMPRODUCT(SUMIF(INDIRECT("'" & sheet & "'!$Z:$Z"),"Dogma",INDIRECT("'" & sheet & "'!AA:AA")))

---

For Reference:

I have three sheets exactly like this:

So you can see it is returning the correct value.

Comments

[coblenski]

Thanks Scott, but that doesn't seem to be working. I'm just getting a bunch of zeroes.

[Scott Craner]

Then check you data. Make sure the sheets in the range are spelled exactly like they are on the tabs. Make sure the data you are looking for is exactly the same no spaces or other characters. Lastly check to make sure the values to be summed are actual numbers and not text.

[Scott Craner]

@JohnCobby see edit for validation.

[coblenski]

Changed formatting to numbers and bam! Thanks!!