Prev: Excel 2007 display and print are different for different users
Next: Excel - Negative nested if then conditions
From: Damian on 5 Feb 2010 19:00
OK I started a question earlier about this and I got it answered BUT now I
would like to modify it further.

Here it is:
=IF(C2=1,SUM(First:Last!A1),"")

What I need this function to do is for each worksheet check if cell C1 has 1
in it and if if does include it in the sum.

This function right now only checks C1 in the worksheet I have the function
in.

I need it to be something like this(I know this is wrong)

=IF((First:Last!C2=1),SUM(First:Last!A1),"")

Thank You

From: T. Valko on 5 Feb 2010 21:55
>I started a question earlier about this and I got
>it answered BUT now I would like to modify
>it further.
>I need it to be something like this(I know this is wrong)
>=IF((First:Last!C2=1),SUM(First:Last!A1),"")

Basically, what you want is a SUMIF across sheets. I foind and read your
earlier post where you say you don't know how many sheets will be involved.
To use a SUMIF across sheets you'd *have* to know how many sheets need to be
included and what the sheet names are.

So, if I were you I'd use a formula on each of the sheets in the same cell
like this:

=(C2=1)*A1

Then, you can use another one of these formulas to get the grand total from
all the sheets:

=SUM(First:Last!X1)

Where X1 on each sheet holds the formula =(C2=1)*A1.

--
Biff
Microsoft Excel MVP


"Damian" <Damian(a)discussions.microsoft.com> wrote in message
news:F405BB0D-0881-4305-BA3E-B719578ECCF5(a)microsoft.com...
> OK I started a question earlier about this and I got it answered BUT now I
> would like to modify it further.
>
> Here it is:
> =IF(C2=1,SUM(First:Last!A1),"")
>
> What I need this function to do is for each worksheet check if cell C1 has
> 1
> in it and if if does include it in the sum.
>
> This function right now only checks C1 in the worksheet I have the
> function
> in.
>
> I need it to be something like this(I know this is wrong)
>
> =IF((First:Last!C2=1),SUM(First:Last!A1),"")
>
> Thank You
>


 | 
Pages: 1
Prev: Excel 2007 display and print are different for different users
Next: Excel - Negative nested if then conditions