if statements

[Guest]

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)

 

[Guest]

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)

 

[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

 

[Guest]

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

 

[Don Guillett]

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

 

[Guest]

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

 

[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!