Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count if problems
I am trying to count the number of cells that contain the number 1 within the
data below. The formula that I am using is =COUNTIF(A1:A14,"1"). With this formula the value returned to me is 2 however it should be 5. Please Help!! 2,3 2 2 1,2,3 3 2 2,3 1,2 2 1,2 3 1 1 2 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count if problems
Based on your posted sample where the numbers are only 1, 2 or 3.
=SUMPRODUCT(LEN(A2:A15)-LEN(SUBSTITUTE(A2:A15,1,""))) Note that this won't work if you want to count 1s and have numbers like: 10, 11, 21, 101, etc. -- Biff Microsoft Excel MVP "mjlunt" wrote in message ... I am trying to count the number of cells that contain the number 1 within the data below. The formula that I am using is =COUNTIF(A1:A14,"1"). With this formula the value returned to me is 2 however it should be 5. Please Help!! 2,3 2 2 1,2,3 3 2 2,3 1,2 2 1,2 3 1 1 2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count if problems
If cells with multiple numbers always begin with the lowest number then you
can use the following: =SUMPRODUCT(--(LEFT(F23:F36,1)="1")) "mjlunt" wrote: I am trying to count the number of cells that contain the number 1 within the data below. The formula that I am using is =COUNTIF(A1:A14,"1"). With this formula the value returned to me is 2 however it should be 5. Please Help!! 2,3 2 2 1,2,3 3 2 2,3 1,2 2 1,2 3 1 1 2 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count if problems
Your formula should state: =COUNTIF(A1:A14,2) then your answer will be 5.
"mjlunt" wrote: I am trying to count the number of cells that contain the number 1 within the data below. The formula that I am using is =COUNTIF(A1:A14,"1"). With this formula the value returned to me is 2 however it should be 5. Please Help!! 2,3 2 2 1,2,3 3 2 2,3 1,2 2 1,2 3 1 1 2 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count if problems
This formula will count 1s
=SUMPRODUCT(--ISNUMBER(FIND("1,",A1:A14&","))) It can be easily changed to count 2 or 12 without counting the 2 in 12..... "wd" wrote: Your formula should state: =COUNTIF(A1:A14,2) then your answer will be 5. "mjlunt" wrote: I am trying to count the number of cells that contain the number 1 within the data below. The formula that I am using is =COUNTIF(A1:A14,"1"). With this formula the value returned to me is 2 however it should be 5. Please Help!! 2,3 2 2 1,2,3 3 2 2,3 1,2 2 1,2 3 1 1 2 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count if problems
=COUNTIF(A1:A14,"*1*").
-- __________________________________ HTH Bob "mjlunt" wrote in message ... I am trying to count the number of cells that contain the number 1 within the data below. The formula that I am using is =COUNTIF(A1:A14,"1"). With this formula the value returned to me is 2 however it should be 5. Please Help!! 2,3 2 2 1,2,3 3 2 2,3 1,2 2 1,2 3 1 1 2 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count if problems
That'll miss cells which contain just the single entry 1 (if a single entry
has not been formatted as text). -- Biff Microsoft Excel MVP "Bob Phillips" wrote in message ... =COUNTIF(A1:A14,"*1*"). -- __________________________________ HTH Bob "mjlunt" wrote in message ... I am trying to count the number of cells that contain the number 1 within the data below. The formula that I am using is =COUNTIF(A1:A14,"1"). With this formula the value returned to me is 2 however it should be 5. Please Help!! 2,3 2 2 1,2,3 3 2 2,3 1,2 2 1,2 3 1 1 2 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count if problems
COUNTIF and SUMIF are crappy functions,
they count the below regardless if text or numeric and if you remove the quotations they do the same but use a wildcard and it looks for text only COUNTIF(A1:A10,"1") -- Regards, Peo Sjoblom "T. Valko" wrote in message ... That'll miss cells which contain just the single entry 1 (if a single entry has not been formatted as text). -- Biff Microsoft Excel MVP "Bob Phillips" wrote in message ... =COUNTIF(A1:A14,"*1*"). -- __________________________________ HTH Bob "mjlunt" wrote in message ... I am trying to count the number of cells that contain the number 1 within the data below. The formula that I am using is =COUNTIF(A1:A14,"1"). With this formula the value returned to me is 2 however it should be 5. Please Help!! 2,3 2 2 1,2,3 3 2 2,3 1,2 2 1,2 3 1 1 2 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count if problems
COUNTIF and SUMIF
They sure are loaded with nuance, especially COUNTIF. -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... COUNTIF and SUMIF are crappy functions, they count the below regardless if text or numeric and if you remove the quotations they do the same but use a wildcard and it looks for text only COUNTIF(A1:A10,"1") -- Regards, Peo Sjoblom "T. Valko" wrote in message ... That'll miss cells which contain just the single entry 1 (if a single entry has not been formatted as text). -- Biff Microsoft Excel MVP "Bob Phillips" wrote in message ... =COUNTIF(A1:A14,"*1*"). -- __________________________________ HTH Bob "mjlunt" wrote in message ... I am trying to count the number of cells that contain the number 1 within the data below. The formula that I am using is =COUNTIF(A1:A14,"1"). With this formula the value returned to me is 2 however it should be 5. Please Help!! 2,3 2 2 1,2,3 3 2 2,3 1,2 2 1,2 3 1 1 2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Employee Work Time - Don't Count Duplicates | Excel Worksheet Functions | |||
Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Count problems[Excel 97] | Excel Discussion (Misc queries) |