# If and Countif

J

#### jamalhakem

Hi
Any help?
I want to count cells that contain numbers with criteria, I used these
functions, but it returns false, the range can contain data or are
blank, I want to count the nonblank only that meet the criteria <50 or
<60. What I wanted is to count if the cells contain numbers, and not
count the blank cells, and if all cells are empty, return blank””.
30 50 60 50 70 80 60 20 50 60 60 80 90

IF(ISNUMBER(D11:F11);COUNTIF(D11:F11;"<50")
+IF(ISNUMBER(G11:J12);COUNTIF(G11:J11;"<60")
+IF(ISNUMBER(K11:L11);COUNTIF(K11:L11;"<50")
+IF(ISNUMBER(M1111);COUNTIF(M1111;"<60")
+IF(ISNUMBER(Q11:S11);COUNTIF(Q11:S11;"<50")
+IF(ISNUMBER(T11:W11);COUNTIF(T11:W11;"<60")
+IF(ISNUMBER(X11:Y11);COUNTIF(X11:Y11;"<50");"")))))))
Jam

S

#### ShaneDevenshire

Hi,

This is easier in 2007 but in 2003 something like this would work
=IF(SUM(ISNUMBER(G11:J11)*(G11:J11<60),ISNUMBER(K11:L11)*(K11:L11<50),ISNUMBER(M1111)*(M1111<60),ISNUMBER(Q11:S11)*(Q11:S11<50),ISNUMBER(T11:W11)*(T11:W11<60),ISNUMBER(X11:Y11)*(X11:Y11<50))=0,"",SUM(ISNUMBER(G11:J11)*(G11:J11<60),ISNUMBER(K11:L11)*(K11:L11<50),ISNUMBER(M1111)*(M1111<60),ISNUMBER(Q11:S11)*(Q11:S11<50),ISNUMBER(T11:W11)*(T11:W11<60),ISNUMBER(X11:Y11)*(X11:Y11<50)))

You can simplify this by formatting the cell with a custom format of
#,##0_);(#,##0);;

=SUM(ISNUMBER(G11:J11)*(G11:J11<60),ISNUMBER(K11:L11)*(K11:L11<50),ISNUMBER(M1111)*(M1111<60),ISNUMBER(Q11:S11)*(Q11:S11<50),ISNUMBER(T11:W11)*(T11:W11<60),ISNUMBER(X11:Y11)*(X11:Y11<50))

Both of these formulas require array entry, press Shift Ctrl Enter to enter
them, not just Enter.

T

#### T. Valko

What kind of numbers are you dealing with? Are they integers (whole numbers)
?

If they're integers, try this:

=IF(COUNT(D11:Y11),FREQUENCY((D11:F11,K11:L11,Q11:S11,X11:Y11),50-0.1)+FREQUENCY((G11:J11,M1111,T11:W11),60-0.1),"")

--
Biff
Microsoft Excel MVP

Hi
Any help?
I want to count cells that contain numbers with criteria, I used these
functions, but it returns false, the range can contain data or are
blank, I want to count the nonblank only that meet the criteria <50 or
<60. What I wanted is to count if the cells contain numbers, and not
count the blank cells, and if all cells are empty, return blank””.
30 50 60 50 70 80 60 20 50 60 60 80 90

IF(ISNUMBER(D11:F11);COUNTIF(D11:F11;"<50")
+IF(ISNUMBER(G11:J12);COUNTIF(G11:J11;"<60")
+IF(ISNUMBER(K11:L11);COUNTIF(K11:L11;"<50")
+IF(ISNUMBER(M1111);COUNTIF(M1111;"<60")
+IF(ISNUMBER(Q11:S11);COUNTIF(Q11:S11;"<50")
+IF(ISNUMBER(T11:W11);COUNTIF(T11:W11;"<60")
+IF(ISNUMBER(X11:Y11);COUNTIF(X11:Y11;"<50");"")))))))