Condition sum

[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!

[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"))

--
HTH

Bob Phillips

"daveks" <[email protected]> wrote in
message news:[email protected]...
>
> 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!
>
>
> --
> daveks
> ------------------------------------------------------------------------
> daveks's Profile:
http://www.excelforum.com/member.php...o&userid=27498
> View this thread: http://www.excelforum.com/showthread...hreadid=470124
>

[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