Search code examples
excelcountifisnumeric

Excel check if value is number inside COUNTIFS

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,"")
Solution

  • 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))