Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If with countif or counta
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 with countif or counta
See your other post
-- 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If with countif or counta
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If with countif or counta
=IF(COUNT(D11:Y11),SUM(COUNTIF(INDIRECT({"D11:F11" ,"K11:L11","Q11:S11","X11:Y11"}),"<50"),COUNTIF(IN DIRECT({"G11:J11","M11:P11","T11:W11"}),"<60")),"" )
" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Help based around countif and counta | Excel Discussion (Misc queries) | |||
countif counta with multiple lookup criteria | Excel Discussion (Misc queries) | |||
Errors in COUNT, COUNTA, COUNTIF when counting merged cells | Excel Worksheet Functions | |||
CountIF, CountA,Which one? or neither? | Excel Discussion (Misc queries) | |||
COUNTA, COUNTIF? | Excel Worksheet Functions |