software

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=quot; quot;,'sheet1:sheet7!c2,quot;0quot;)
I do this and I get a ref error. How else can I type this?

Thanks

Why not just sim em
=sum(sheet1:sheet7!c2)--
Don Guillett
SalesAid Software

quot;Mergequot; gt; wrote in message
...
gt;I have 7 sheets in a workbook, I would like to
gt; if all seven sheets cell a2 = a blank cell return the total amt in cell
gt; c2(in each sheer c2 is a sum of 3 other columns. iF not enter a zero amt.
gt;
gt; example -if('sheet1:sheet7'!a2=quot; quot;,'sheet1:sheet7!c2,quot;0quot;)
gt; I do this and I get a ref error. How else can I type this?
gt;
gt; Thanks
=if(and(sheet1!a2=quot;quot;,sheet2!a2=quot;quot;,sheet2!a3=quot;quot;,she et2!a4=quot;quot;,sheet2!a5=quot;quot;,sheet6!a2=quot;quot;,sheet2!a7=quot;quot;), quot;If its truequot;,quot;if its falsequot;)
Originally Posted by MergeI 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=quot; quot;,'sheet1:sheet7!c2,quot;0quot;)
I do this and I get a ref error. How else can I type this?

Thanksbecause 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)quot;Don Guillettquot; wrote:

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

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?

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

quot;Pete_UKquot; wrote:

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

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

--
Don Guillett
SalesAid Software

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

Thanksquot;Pete_UKquot; wrote:

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

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

=SUMPRODUCT(--(COUNTIF(INDIRECT(quot;'quot;amp;E2:E8amp;quot;'!A2quot;),quot;?*quot;)=0),N(IND IRECT(quot;'quot;
amp;E2:E8amp;quot;'!C2quot;)))

Hope this helps!

In article gt;,
Merge gt; wrote:

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