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(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
 
Ad

Advertisements

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(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: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)))

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(M11:p11)*(M11:p11<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.
 
Ad

Advertisements

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,M11:p11,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(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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

If with countif or counta 3
Evaluating more than 30 conditions using a formula 1
CountIF question 5
If and or 2
Countif does not work 2
CountIf & multiple criteria 1
Cummulative Totals 4
Using AND OR 4

Top