Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If and Countif
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If and Countif
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If and Countif
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP! countif | Excel Worksheet Functions | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |