Sum across sheets based on a condition

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GoBow777 - 07 Mar 2008 23:56 GMT

Hello; I’m trying to sum cells A1:A10 across several sheets no problem.
However, if cell B1 on each of the sheets does not equal the value of 1
found on the Summary sheet, then those sheets would not be added.

In other words if Sheet3 cell B1 has a value of 2, then Sheet3 would
not be included in the summing process, but Sheet2, Sheet4 through
Sheet40 would be.

Keep in mind that the range A1:A10 has to show their values regardless
of the value of cell B1.

How can this be done?

Signature

GoBow777

Reply to this Message

T. Valko - 08 Mar 2008 02:53 GMT

Let's take the easy way on this!

On *each* sheet, in the same cell enter this formula:

=(B1=1)*SUM(A1:A10)

You can group the sheets together and enter the formula on one sheet and it
will be entered on *all grouped* sheets.

Let's assume you enter that formula on each sheet in cell A11. Then:

=SUM(Sheet1:Sheet40!A11)

Signature

Biff
Microsoft Excel MVP

> Hello; I'm trying to sum cells A1:A10 across several sheets no problem.
> However, if cell B1 on each of the sheets does not equal the value of 1
[quoted text clipped - 8 lines]
>
> How can this be done?

Reply to this Message

GoBow777 - 08 Mar 2008 18:09 GMT

Thanks Biff!

I was hoping for a single formula but this does the job just fine.

Mat

--
GoBow777

Reply to this Message

T. Valko - 08 Mar 2008 20:21 GMT

You're welcome. Thanks for the feedback!

Signature

Biff
Microsoft Excel MVP

> Thanks Biff!
>
> I was hoping for a single formula but this does the job just fine.
>
> Matt

Reply to this Message