Sumproduct across multiple sheets

[robcarr]

Hi,

I am using this formula successfully to calcualte all data in column BB for each day, H1-AL1 have my sheets names in.

=SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$AL$1&"'!a2:a500"),$B2,INDIRECT("'"&$H$1:$AL$1&"'!bb2:bb500")))

I need to adjust it to sum all data in bb it AZ has a "T", but i cannot get it to work, I get a #value!

=SUMPRODUCT((INDIRECT("'"&$H$1:$AL$1&"'!a2:a500")=B2)*(INDIRECT("'"&$H$1:$AL$1&"'!bb2:bb500")="T")*(INDIRECT("'"&$H$1:$AL$1&"'!az2:az500")))

any help on this is greatly appreciated.



Hope this is of use, Rob.

 

[GlennUK]

Are you saying that you want to sum all of col BB when col AZ has a "T"? But your formula is aiming for summing AZ when BB has a T!?!?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[robcarr]

hi,

sorry column A has a text, column AZ has a "T" and BB has numbers.

I need to calculate all BB if other 2 conditions are met, accroos all sheets.

I did get the columns mixed up but still get #value!

each sheet has different length of data, "T" may not appear on all sheets.

Hope this is of use, Rob.

 

[GlennUK]

Moving the logic outside of a SUMIF complicates things, but this might do it:

=SUMPRODUCT((T(OFFSET(INDIRECT("'"&$H$1:$AL$1&"'!A1"),ROW(INDIRECT("1:500")),0))=B2)*(T(OFFSET(INDIRECT("'"&$H$1:$AL$1&"'!AZ1"),ROW(INDIRECT("1:500")),0))="T"),N(OFFSET(INDIRECT("'"&$H$1:$AL$1&"'!BB1"),ROW(INDIRECT("1:500")),0)))

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

Your life would be sooooooo much easier, if all your similar data were in ONE sheet in ONE table. THAT is how Excel is designed to function. Chopped up data is akin to having one hand tied behind your back, one eye covered, feet in a sack and the other hand with an oven mitten. I sure wouldn't let that inhibit me.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}