Excel check if value is number inside COUNTIFS

3
votes

I'm trying to count the number of cells in a row that contain "valid" values. There are many criteria for the value to be valid. So far I have this:

COUNTIFS(INDEX(S2ScoresAssQ1,ROW()-4,0),"<>",S2UnitSelectorQ1,"<>2",S2CodeAssQ1,O$2)

but cells that meet all of the requirements and contain formulas that evaluate to "" are still being counted, when they should not be. In other words, some of the cells contain this formula:

=IF(H5<>"",H5*I$3/H$3,"")

If that evaluates to a number, it should be counted. If it evaluates to "", then it should NOT be counted. ISNUMBER would work, but I don't know how to put that inside the COUNTIFS.

Here is an example:

enter image description here Cell F4 should display '1', since there is only one valid 'A' assignment. The code in column F (cell F3) is:

=COUNTIFS(B$2:E$2,F$2,B3:E3,"<>")

The code is column E (cell E3) is:

=IF(ISNUMBER(D3),D3/12,"")
1
excelcountifisnumeric
Try =COUNTIFS(B$2:E$2,F$2,B4:E4,"<>*",B4:E4,"<>") – Tom Sharpe
Yes, that seems to work. Thanks! – user3925803
The "<>*" part doesn't make sense to me. What does that mean? – user3925803
The asterisk is a wildcard meaning 'any text' but doesn't include numbers. For some reason "<>" only excludes truly empty cells in COUNTIFS so to exclude ones that have a formula in them (even if the result is "") you have to use "<>*". – Tom Sharpe

1 Answers

1
votes

As Tom Sharpe said, using:

=COUNTIFS(B$2:E$2,F$2,B4:E4,"<>*",B4:E4,"<>")

works in the example, with "<>" eliminating the truly blank cells and "<>*" eliminating the cells that contain formulas that evaluate to "" (while leaving the formulas that evaluate to a number).

I was able to solve the problem in my original code using SUMPRODUCT():

=SUMPRODUCT(--ISNUMBER(INDEX(S2ScoresAssQ1,ROW()-4,0)),--(S2UnitSelectorQ1<>2),--(S2CodeAssQ1=O$2))