if statements

[Merge]

I have 7 sheets in a workbook, I would like to
if all seven sheets cell a2 = a blank cell return the total amt in cell
c2(in each sheer c2 is a sum of 3 other columns. iF not enter a zero amt.

example -if('sheet1:sheet7'!a2=" ",'sheet1:sheet7!c2,"0")
I do this and I get a ref error. How else can I type this?

Thanks

[Don Guillett]

Why not just sim em
=sum(sheet1:sheet7!c2)


--
Don Guillett
SalesAid Software
[email protected]
"Merge" <[email protected]> wrote in message
news:[email protected]...
>I have 7 sheets in a workbook, I would like to
> if all seven sheets cell a2 = a blank cell return the total amt in cell
> c2(in each sheer c2 is a sum of 3 other columns. iF not enter a zero amt.
>
> example -if('sheet1:sheet7'!a2=" ",'sheet1:sheet7!c2,"0")
> I do this and I get a ref error. How else can I type this?
>
> Thanks

[Merge]

because I want it to update atuomatically. if A2 references work completed
then I wouldn't want to include that . So I am am saying if A2= balnk cell
them bring over the data from C2(which happens to be a result of a formula)


"Don Guillett" wrote:

> Why not just sim em
> =sum(sheet1:sheet7!c2)
>
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Merge" <[email protected]> wrote in message
> news:[email protected]...
> >I have 7 sheets in a workbook, I would like to
> > if all seven sheets cell a2 = a blank cell return the total amt in cell
> > c2(in each sheer c2 is a sum of 3 other columns. iF not enter a zero amt.
> >
> > example -if('sheet1:sheet7'!a2=" ",'sheet1:sheet7!c2,"0")
> > I do this and I get a ref error. How else can I type this?
> >
> > Thanks
>
>
>

[Pete_UK]

Sorry, I'm a bit confused between your two postings. In your first post
you seem to be saying that if every A2 cell on the seven sheets is
blank, then you want the sum of all the C2 cells, otherwise you want
zero (so if you have only 6 or fewer blanks then you want a zero).

Now you seem to be saying that if A2 is blank on any sheet, then add C2
from that sheet (so if you have 5 blanks, then you will have five C2
cells added together).

Which is it to be?

Pete

[Gerry-W]

=if(and(sheet1!a2="",sheet2!a2="",sheet2!a3="",sheet2!a4="",sheet2!a5="",sheet6!a2="",sheet2!a7=""),"If
its true","if its false")


Merge Wrote:
> I have 7 sheets in a workbook, I would like to
> if all seven sheets cell a2 = a blank cell return the total amt in
> cell
> c2(in each sheer c2 is a sum of 3 other columns. iF not enter a zero
> amt.
>
> example -if('sheet1:sheet7'!a2=" ",'sheet1:sheet7!c2,"0")
> I do this and I get a ref error. How else can I type this?
>
> Thanks


--
Gerry-W

[Merge]

yes you 've got it my explanation was not clear the first time.
Thank you

"Pete_UK" wrote:

> Sorry, I'm a bit confused between your two postings. In your first post
> you seem to be saying that if every A2 cell on the seven sheets is
> blank, then you want the sum of all the C2 cells, otherwise you want
> zero (so if you have only 6 or fewer blanks then you want a zero).
>
> Now you seem to be saying that if A2 is blank on any sheet, then add C2
> from that sheet (so if you have 5 blanks, then you will have five C2
> cells added together).
>
> Which is it to be?
>
> Pete
>
>

[Don Guillett]

Don't you just love these great answers when you ask the OP a question for
their own good.

--
Don Guillett
SalesAid Software
[email protected]
"Pete_UK" <[email protected]> wrote in message
news:[email protected]...
> Sorry, I'm a bit confused between your two postings. In your first post
> you seem to be saying that if every A2 cell on the seven sheets is
> blank, then you want the sum of all the C2 cells, otherwise you want
> zero (so if you have only 6 or fewer blanks then you want a zero).
>
> Now you seem to be saying that if A2 is blank on any sheet, then add C2
> from that sheet (so if you have 5 blanks, then you will have five C2
> cells added together).
>
> Which is it to be?
>
> Pete
>

[Merge]

I was not clear the first time.
it is....
if A2 is blank on any sheet, then add C2 from that sheet (so if you have 5
blanks, then you will have five C2 cells added together).

Thanks


"Pete_UK" wrote:

> Sorry, I'm a bit confused between your two postings. In your first post
> you seem to be saying that if every A2 cell on the seven sheets is
> blank, then you want the sum of all the C2 cells, otherwise you want
> zero (so if you have only 6 or fewer blanks then you want a zero).
>
> Now you seem to be saying that if A2 is blank on any sheet, then add C2
> from that sheet (so if you have 5 blanks, then you will have five C2
> cells added together).
>
> Which is it to be?
>
> Pete
>
>

[Domenic]

Let E2:E8 contain the sheet names, then try the following formula...

=SUMPRODUCT(--(COUNTIF(INDIRECT("'"&E2:E8&"'!A2"),"?*")=0),N(INDIRECT("'"
&E2:E8&"'!C2")))

Hope this helps!

In article <[email protected]>,
Merge <[email protected]> wrote:

> if A2 is blank on any sheet, then add C2 from that sheet (so if you have 5
> blanks, then you will have five C2 cells added together).
>
> Thanks