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

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

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


--
Don Guillett
SalesAid Software

"Merge" wrote in message
...
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

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

"Merge" wrote in message
...
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

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

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't you just love these great answers when you ask the OP a question for
their own good.

--
Don Guillett
SalesAid Software

"Pete_UK" wrote in message
oups.com...
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

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

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

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

Hope this helps!

In article ,
Merge 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