Adding a second criteria to indirect Sumproduct

[cjwenngatz]

Sorry for all the questions lately - this project is really testing my
skills :S

I have a formula I'm using to SUMIF multiple data across various
sheets:
=SUMPRODUCT(SUMIF(INDIRECT({"18","19","20","21","22","23","24"}&"!
B16:B300"),A15,INDIRECT({"18","19","20","21","22","23","24"}&"!
W16:W300")))

However, I'm looking to add a second criteria to this. Right now the
data in range B16:B300 has to match A15, but I also need to add a
second criteria where the data in column C has to match A16.

Any ideas?

 

[T. Valko]

This could be done in a single formula however that formula would be
mind-boggling complex, very long and not very efficient.

Your best option is to put a separate formula on each sheet in the same cell
then use a simple SUM function to get a total from all the sheets.

For example, in the same cell on each sheet:

=SUMPRODUCT(--(B16:B300=A15),--(C16:C300=A16),W16:W300)

Then, to get the total of all sheets:

Assuming the above formula is in cell A1 of each sheet.

=SUM('18:24'!A1)