ExcelBanter - If and Countif

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - If and Countif (https://www.excelbanter.com/excel-worksheet-functions/204285-if-countif.html)

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(M11:P11);COUNTIF(M11:P11;"<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

Hi,

This is easier in 2007 but in 2003 something like this would work:
=IF(SUM(ISNUMBER(G11:J11)*(G11:J11<60),ISNUMBER(K1 1:L11)*(K11:L11<50),ISNUMBER(M11:P11)*(M11:P11<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:L1 1)*(K11:L11<50),ISNUMBER(M11:P11)*(M11:P11<60),ISN UMBER(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:L 11)*(K11:L11<50),ISNUMBER(M11:P11)*(M11:P11<60),IS NUMBER(Q11:S11)*(Q11:S11<50),ISNUMBER(T11:W11)*(T1 1: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.
--
Thanks,
Shane Devenshire

" wrote:

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(M11:P11);COUNTIF(M11:P11;"<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

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,M11:P11,T11:W11),60-0.1),"")

--
Biff
Microsoft Excel MVP

wrote in message
...
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(M11:P11);COUNTIF(M11:P11;"<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