If and Countif

[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");"")))))))
Thanks in advance
Jam

 

[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);;

Then the formula would read:

=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. 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");"")))))))
Thanks in advance
Jam