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!

 

[MarkNez]

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

AlphaFrog,
I am using code
=COUNTIFS('Sheet4'!F8:F389,"yes", 'Sheet4'!M8:M389, "DEM1")

What needs to be changed if I want to sum the "blanks" or "null" cells in 'Sheet4'!F8:F389 as well as the "Yeses"?

Basically the formula you gave me sums all the rows that have a "Yes" in Cell F8-F389 and "DEM1" in Cell M8-M389. Now I want to sum all the cells left blank (or with no data entered) as a "Yes"...but only if Cell M8-M389 has "DEM1" in it...does that make sense?

I tried a few different ways but keep getting "False" or "Name" errors.

Thanks again!

 

[AlphaFrog]

This should do it
=COUNTIFS('Sheet4'!F8:F389,"=", 'Sheet4'!M8:M389, "DEM1")

 

[Matty]

Not sure how to do this with COUNTIFS, but this should work for you:

=SUMPRODUCT(('Sheet4'!F8:F389="Yes")+('Sheet4'!F8:F389=""),--('Sheet4'!M8:M389="DEM1"))

Matty

 

[MarkNez]

This should do it
=COUNTIFS('Sheet4'!F8:F389,"=", 'Sheet4'!M8:M389, "DEM1")

Your the BEST! Thanks again for the help. That worked perfectly.

 

[MarkNez]

Not sure how to do this with COUNTIFS, but this should work for you:

=SUMPRODUCT(('Sheet4'!F8:F389="Yes")+('Sheet4'!F8:F389=""),--('Sheet4'!M8:M389="DEM1"))

Matty

Thanks for the help Matty. I am using MAC OSX 2011 and went with AlphaFrogs code because he has helped throughout this thread on a few other items. I didn't want to start mixing =COUNTIFS and =SUMPRODUCTS if I didn't have to

Thank again for your help though!!