Counting Based On Multiple Conditions

[salvatore]

I am attempting to count a series of cells based on their content. More
specifically, I want to count each that does not have either a null
value or an "N/A" in it. Everything else is fair game.

Here's the closest I've come so far:
=COUNTIF(C129:C131,OR(C129:C131 <>"",C129:C131,"<>N/A"))

The idea behind this is:
Count only those cells in range C129 to C131 where,
* The cell is not null, or
* The cell does not contain "N/A"

Sample record set:
C129 - Countme
C130 - NULL
C131 - N/A

Desired result set:
1

Am I close? Or playing deep left field?

 

[Frank Kabel]

Hi
try:
=SUMPRODUCT(--(C129:C131<>""),--(C129:C131"<>N/A"))

also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[salvatore]

Hi
try:
=SUMPRODUCT(--(C129:C131<>""),--(C129:C131"<>N/A"))

also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Frank,

I believe you've remedied my formula issues before with sumproduct, but
this one is still throwing an error.
When I enter the above into a cell, Excel tells me there's an error and
highlights the second :C131.

Sorry to be such a first grader about this, but Im just not seeing where
the problem is. What other background can I give to help troubleshoot this?

 

[Frank Kabel]

Hi
small typo. Try:
=SUMPRODUCT(--(C129:C131<>""),--(C129:C131<>"N/A"))

 

[salvatore]

Hi
small typo. Try:
=SUMPRODUCT(--(C129:C131<>""),--(C129:C131<>"N/A"))

Sorry, I should have caught that. This of course works as intended.
Thanks again Frank.