Excluding Text Entry's in a Countif

[Kobi Merrikin]

Hello!

I am trying to streamline a process I currently use.
I have a column of data returning a compliance score for each customer however if the customer has not had an order it display's "N/A". For example: 42.4, N/A, 100, 59.4, N/A, 75, 100. See picture.
I want to count the amount of customers that are compliant for the week. So those who are above 70 as well as those showing as N/A.
These customers are grouped into 4 buckets (for purposes of the example I will call them red, yellow, green, blue) so I want to count the compliant customers by bucket.
I can return those above 70% however the N/A cells cause a #VALUE! error.
The formula I currently have is
=IF(P2:P308>70,COUNTIF(B:B,"Red"),0)

Can anyone help how I can include the N/A cells?

Thanks in advance!

 

[jasonb75]

Not sure that your formula will do what you think it will anyway, the first part is meaningless without an additional function to qualify the array.

Perhaps one of these? The first one counts greater than 70 in column P and Red in column B, the second counts N/A as well.

=COUNTIFS(P:P,">70",B:B,"Red")

=SUM(COUNTIFS(P:P,{">70","N/A"},B:B,"Red"))