Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in a range (and parts of cells)
Hello,
I've got a row of numbers as shown below. I need to count the number of times a particular number shows up in this column range. But, if I do the usual COUNTIF, I don't get the proper number. I've tried =COUNTIF(A1:A10,"147"), but only come up with 2. I need an answer of 6. Any help would be greatly appreciated. 147 148 147 147,150 145,146 147,147 142 143,147 140 136 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in a range (and parts of cells)
Use B1 to hold your criteria (147),
Then try this: =SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,B1,"")))/LEN(B1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Phrank" wrote in message ... Hello, I've got a row of numbers as shown below. I need to count the number of times a particular number shows up in this column range. But, if I do the usual COUNTIF, I don't get the proper number. I've tried =COUNTIF(A1:A10,"147"), but only come up with 2. I need an answer of 6. Any help would be greatly appreciated. 147 148 147 147,150 145,146 147,147 142 143,147 140 136 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in a range (and parts of cells)
Hi RD,
Thanks for the formula. I learned something similar the other day - counting "a" in a string. Glad you use SUMPRODUCT instead of SUM and CSE. The only way that the original poster can use COUNTIF is when he/she does "text to columns" first. I tried it on the data set posted and it worked. Don't know the arrangement of all data to tell if it will work. Any comments? Epinn "RagDyer" wrote in message ... Use B1 to hold your criteria (147), Then try this: =SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,B1,"")))/LEN(B1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Phrank" wrote in message ... Hello, I've got a row of numbers as shown below. I need to count the number of times a particular number shows up in this column range. But, if I do the usual COUNTIF, I don't get the proper number. I've tried =COUNTIF(A1:A10,"147"), but only come up with 2. I need an answer of 6. Any help would be greatly appreciated. 147 148 147 147,150 145,146 147,147 142 143,147 140 136 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in a range (and parts of cells)
Works beautifully! Thank you very much.
Frank On Mon, 9 Oct 2006 18:42:57 -0700, "RagDyer" wrote: Use B1 to hold your criteria (147), Then try this: =SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,B1,"")))/LEN(B1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup Error in Part of a Named Range | Excel Worksheet Functions | |||
Delete range on another sheet when checkbox is unchecked | Excel Discussion (Misc queries) | |||
Merging Info in Two Cells | Excel Discussion (Misc queries) | |||
Copying functions from one cell to other cells | New Users to Excel | |||
Vlookup of parts of a word in a range of Data | Excel Discussion (Misc queries) |