Multi-conditional sum across multiple sheets.

[cartja]

=SUMPRODUCT(SUMIF(INDIRECT(""&list&"!J$22"),"=1",INDIRECT(""&list&"!J4")))

This formula works great for summing cell J4 across 94 sheets based on the condition that J22=1. However, I can't figure out how to add a second condition - K22=1.

If anyone could help me modify this formula so that it will check both conditions, (cell1=1, AND Cell2=1) before adding j4 to the sum across 94 sheets I would be very happy!.

Oh, sheet names are housed in a list called 'list'.

Thanks,

j-bob

 

[pgc01]

Hi j-bob
Welcome to the board

Please always post your excel version. One easy way is to add it to your signature.

If you have excel 2007+ you can try the same formula using SUMIFS().

 

[cartja]

Thank you pgc01! This worked great. I didn't know sumifs exited. It sure adds a new dimesion of computing!

I am using excel 2007.

Do you know why the formula won't drag across or down as a dynamic DNA? The way its writen now, its acting like a specific refernce formula.

Thanks,

j-bob

 

[pgc01]

Hi j-bob

I'm glad it helped.

Do you know why the formula won't drag across or down as a dynamic DNA? The way its writen now, its acting like a specific refernce formula.

The only thing that changes when you drag formulas are relative addresses. What you have in your formula are strings.

For ex. if you have in a cell ="A1", this won't change when you drag the formula, because it's a string.

If you want the formula to change when you drag it, you have to introduce relative references in it.

For ex., in the formula you posted you had the strings "J$22" and "J4", that would then be converted into references by Indirect(). If you want the row in J4 to change when you drag it, you can use:

=SUMPRODUCT(SUMIF(INDIRECT("'"&List&"'!J$22"),"=1",INDIRECT("'"&List&"'!J"&ROW(J4))))

Now when you drag the formula the Row(J4) will change, thus changing the input string for Indirect().

Hope it's clear.

Please test.

 

[cartja]

Thank you PGC! It works!

Take care,

j-bob