Condition sum

D

daveks

Hi
My workbook has in access of 100 sheets, cells b1 contains text in
every worksheet and c2 a numerical value (sometimes 0 or blank). I wish
to sum c2 for all identical text in b2. I have tried sumif but
understand this does not work over different sheets.
For info there are only 5 variants of text in b2
Excel version 97
I feel this should be simple but have fail miserably help!
 
B

Bob Phillips

Dave,

Here is how to do it.

Load the sheet names into an array, C1:C100

=SUMPRODUCT(N(INDIRECT("'"&C1:C100&"'!c2"))*(T(INDIRECT("'"&C1:C100&"'!b2"))
<="text"))
 
D

daveks

Bob

A big thank you worked a treat. Have to put hand up and admit I dont
understand it yet but feel better because it wasnt as simple as I
assumed.
Once again thank you.
Cheers.

Dave
 
Top