Sumif...else

[jdderrig]

I have 5 cells that I would like to sum only if they contain a number. If any contain text, I want the value to return as "NQ"

Cells:
G10:K10 will either contain text (Select One or N/A) or a number from 0-10

Is there a way to write a formula that says: sum if G10:K10 is >=0 else return "NQ"

Thanks

 

[Joe4]

Welcome to the Board!

Is this what you are looking for?
=IF(SUM(G10:K10)>0,SUM(G10:K10),"NQ")

The only thing I am not quite clear on is what you want to happen if there are numbers AND text in that range?
Do you want it to just sum up the numbers, or if any cell contains text, do you want to return "NQ" (even if the other cells have numbers)?
My formula above is working under the assumption that you want to add up any numbers in that range, even if some cells have text.

 

[jdderrig]

OMG...thank you so much. I had tried everything under the sun - this works!!

 

[Fluff]

Hi & welcome to MrExcel.
How about
=IFERROR(1/(1/SUM(G10:K10)),"NQ")

 

[Joe4]

You are welcome.

 

[Fluff]

Oops, bit slow today

 

[jdderrig]

OMG...thank you so much. I had tried everything under the sun - this works!!

Sorry, spoke too soon. I need it to be greater than or equal to 0. This only works if it is >0

 

[jdderrig]

Sorry, spoke too soon. I need it to be greater than or equal to 0. This only works if it is >0

It also brings back 0 if I select N/A for the cells, not NQ

 

[Joe4]

Sorry, spoke too soon. I need it to be greater than or equal to 0. This only works if it is >0

That gets a bit trickier, because if you have ten text entries, SUM will return 0, and if you have 10 entries of 0, the SUM of that will also return zero.
So, then it will probably need to look at the entries in the cell and count how many are numeric (or non-numeric).

Does every cell have to have a text entry or a wonder?
Or could any be blank?

 

[Joe4]

Assuming that no cells can be blank, see if this does it:
=IF(COUNT(G10:K10)>0,SUM(G10:K10),"NQ")

That will return the SUM if at least one number appears in the range (note that the COUNT function only counts numeric entries).

If ALL 10 entries must be numeric, then use:
=IF(COUNT(G10:K10)=10,SUM(G10:K10),"NQ")