Trying to sum a cell when text or a "blank cell" is identified.

[MarkNez]

Hi Everyone,
I posted a thread over the weekend trying to create a sum from cell A7 when text is detected in A9 (my original question:
"I have a small table, in cell A7 I have a sum, in cell A9 I have either "Yes" or "No" displayed (from the question above), and what I need in cell A10 is a formula that displays the sum in cell A7 if cell A9 is "Yes" and displays "0" if cell A9 displays "No"."

I received an answer to that question (Thanks to VoG!!) and it worked great (Answer/formula from VoG
A9 ='Sheet2'!C7
A10 =IF(A9="Yes",A7,0)

Now I need to change it up a little...Cell A9 displays either a Yes, No, or 0. Now I need to treat a "0" like a "Yes" and in cell A10 display the sum from Cell A7 when Cell A9 is either a Yes or a 0 and display 0 when Cell A9 is a No.

Man I hope that makes sense!!!

Thank you all VERY much!

 

[CWatts]

Change

A9="Yes"

to

OR(A9="Yes",A9=0)

If the cell is formatted as text: OR(A9="Yes",A9="0")

 

[AlphaFrog]

Try something like this...
=IF(OR(A9="Yes",A9=0),A7,0)

 

[HalfAce]

Or (and I may be over simplifying the issue here) but perhaps you could just test for the 'No' value in A9. (ie.)
<table border="0" cellpadding="0" cellspacing="0" width="64"><col width="64"><tr height="17"> <td style="height:12.75pt;width:48pt" height="17" width="64">=IF(A9="No",0,A7)</td> </tr></table>

 

[MarkNez]

Man, I am amazed at how smart and fast you guys are! I went with AlphaFrogs formula and it work perfectly!

Thank you guys so much. I wish I could repay you for your efforts!

Many Thanks,
Mark

 

[HalfAce]

I wish I could repay you for your efforts!

Perhaps in doughnuts and coffee???

 

[MarkNez]

Man give me your add and I'll send you a dozen donuts and a Starbucks coffee!!

You guys make me look good! Its worth donuts and coffee!

 

[MarkNez]

One more question (for now):

I have a "summary table" on Sheet 1 that displays the total number of "Yeses" that I have entered on sheet 4 in cells F8 - F389.
This is the code I used for the sum on sheet 1
=COUNTIF('Sheet4'!F8:F389,"yes")

What I need to do now is calculate the "Yeses" in cells F8 - F389 only when the text "DEM1" is in cell M8 - M389.

Thanks again for the help!

Mark

 

[AlphaFrog]

Excel 2007 or later:
=COUNTIFS('Sheet4'!F8:F389,"yes", 'Sheet4'!M8:M389, "DEM1")

Excel 2003 or earlier
=SUMPRODUCT(--('Sheet4'!F8:F389="yes"),--('Sheet4'!M8:M389="DEM1"))


....mmm donuts
-Homer

 

[MarkNez]

Once again! Thanks AlphaFrog...you get two dozen and a

It worked perfectly (using 2011 Mac OSX).