Need help with an excel formula

[joe]

I have a really tough question that I hope someone can help me with.
Basically, I need to sum up data from three worksheets, but only if a
certain condition is met. For example, In 3 worksheets, I have data in
B1, B2, B3. A1, A2 and A3 have 3 names in them in each worksheet. Now,
I know how to take the sum of each worksheet, but I only want to take
the sum of everything excluding cell B2 which is Bill in the example
below.


The data below appears exactly like this in all three worksheets.

sheet1
A1 A2 A3
john bill John

B1 B2 B3
12 45 13

sheet2
A1 A2 A3
john bill John

B1 B2 B3
12 45 13

sheet3
A1 A2 A3
john bill John

B1 B2 B3
12 45 13

 

[T. Valko]

I'm assuming that I should not take your example literally! If I did then
you could do this:

=SUM(Sheet1:Sheet3!B1)+SUM(Sheet1:Sheet3!B3)

Otherwise, try this:

=SUMPRODUCT(SUMIF(INDIRECT("sheet"&{1,2,3}&"!A1:A3"),"<>Bill",INDIRECT("sheet"&{1,2,3}&"!B1:B3")))

Now, if your real sheet names aren't Sheet1, Sheet2, Sheet3...

List the sheet names in a range of cells like H1:H3 then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&H1:H3&"'!A1:A3"),"<>Bill",INDIRECT("'"&H1:H3&"'!B1:B3")))

Biff
Microsoft Excel MVP