Home |
Search |
Today's Posts |
#1
|
|||
|
|||
occurance of numbers in cell range
Hello,
I'm trying to figure out how to calculate the occurance of multiple numbers in multiple cell range. For example: A1 B1 C1 3 11 54 2 5 45 3 11 54 I would like to calculate how many times 3, 11 and 54 came up... In this case 2. |
#2
|
|||
|
|||
Try the following...
=SUM(COUNTIF(A1:C3,{3,11,54})) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Dillenger wrote: Hello, I'm trying to figure out how to calculate the occurance of multiple numbers in multiple cell range. For example: A1 B1 C1 3 11 54 2 5 45 3 11 54 I would like to calculate how many times 3, 11 and 54 came up... In this case 2. |
#3
|
|||
|
|||
Hi ,
Technically it works... BUT Its giving me a result of 8, because its counting each number, not just each row. It should be giving me a result of 2... Thanks for the help though, it will probably help me figure it out. "Domenic" wrote: Try the following... =SUM(COUNTIF(A1:C3,{3,11,54})) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Dillenger wrote: Hello, I'm trying to figure out how to calculate the occurance of multiple numbers in multiple cell range. For example: A1 B1 C1 3 11 54 2 5 45 3 11 54 I would like to calculate how many times 3, 11 and 54 came up... In this case 2. |
#4
|
|||
|
|||
Do you want an individual formula for each column, or a single formula for
the entire range? Individual: =COUNTIF(A:A,3) =COUNTIF(B:B,11) =COUNTIF(C:C,54) OR ... You could enter the number you're looking to count in D1, And then use this formula to return a count of that number in the entire range: =COUNTIF(A:C,D1) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Dillenger" wrote in message ... Hi , Technically it works... BUT Its giving me a result of 8, because its counting each number, not just each row. It should be giving me a result of 2... Thanks for the help though, it will probably help me figure it out. "Domenic" wrote: Try the following... =SUM(COUNTIF(A1:C3,{3,11,54})) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Dillenger wrote: Hello, I'm trying to figure out how to calculate the occurance of multiple numbers in multiple cell range. For example: A1 B1 C1 3 11 54 2 5 45 3 11 54 I would like to calculate how many times 3, 11 and 54 came up... In this case 2. |
#5
|
|||
|
|||
BTW,
*Not* really an array formula. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "Domenic" wrote in message ... Try the following... =SUM(COUNTIF(A1:C3,{3,11,54})) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Dillenger wrote: Hello, I'm trying to figure out how to calculate the occurance of multiple numbers in multiple cell range. For example: A1 B1 C1 3 11 54 2 5 45 3 11 54 I would like to calculate how many times 3, 11 and 54 came up... In this case 2. |
#6
|
|||
|
|||
Is this what you want?
=SUMPRODUCT(--((G17:G20&H17:H20&I17:I20)=(G17&H17&I17))) --2 Ola Sandstrom Note: The '&' concatenates the A, B and C column -- is a way to convert all True to 1 and all False to 0 Sumproduct is "a formula to work with Arrays (many rows/col's at one time)" |
#7
|
|||
|
|||
....and that should be...
=SUMPRODUCT(--( (A1:A3 & B1:B3 & C1:C3) = (A1 & B1 & C1) ) ) I added a few " " to make it easier to read. Ola |
#8
|
|||
|
|||
This is exactly what I need. Thank you all for your help :)
"Ola" wrote: ...and that should be... =SUMPRODUCT(--( (A1:A3 & B1:B3 & C1:C3) = (A1 & B1 & C1) ) ) I added a few " " to make it easier to read. Ola |
#9
|
|||
|
|||
Thanks for the feedback
Ola |
#10
|
|||
|
|||
In article ,
"RagDyeR" wrote: BTW, *Not* really an array formula. Thanks for catching that and bringing it to my attention. Much appreciated. Cheers! |
#11
|
|||
|
|||
"Ola" wrote...
...and that should be... =SUMPRODUCT(--( (A1:A3 & B1:B3 & C1:C3) = (A1 & B1 & C1) ) ) I added a few " " to make it easier to read. Why test A1:A3 against itself? It'll obviously be true. =1+SUMPRODUCT(--((A2:A3&B2:B3&C2:C3)=(A1&B1&C1))) But this is a classic bug in waiting. This may work with the OP's sample data, but will fail in cases like 3 11 54 2 5 45 31 15 4 Far better to keep the comparisons separate. =1+SUMPRODUCT((A2:A3=A1)*(B2:B3=B1)*(C2:c3=C1)) |
#12
|
|||
|
|||
On Sat, 19 Feb 2005 17:38:13 -0800, "Harlan Grove" wrote:
a classic bug in waiting ROFL! I like that! |
#13
|
|||
|
|||
Your right Harlan,
The formula should use '=' and separate the search criteria: =SUMPRODUCT((A1:A3=D1)*(B1:B3=E1)*(C1:C3=F1)) However, I can't see why 1+ would be needed. Ola |
#14
|
|||
|
|||
Ola wrote...
.... =SUMPRODUCT((A1:A3=D1)*(B1:B3=E1)*(C1:C3=F1)) However, I can't see why 1+ would be needed. Now that *YOU* have changed the formula, no reason. However, *YOUR* original formula was =SUMPRODUCT(--( (A1:A3 & B1:B3 & C1:C3) = (A1 & B1 & C1) ) ) and there's no point to including A1, B1 and C1 in the left hand operand to = since A1&B1&C1 necessarily equals itself. So my point was that *THIS* formula (not some new one you through out) could be rewritten as =1+SUMPRODUCT(--((A2:A3&B2:B3&C3:C3)=(A1&B1&C1))) |
#15
|
|||
|
|||
Harlan Grove wrote...
.... =1+SUMPRODUCT(--((A2:A3&B2:B3&C3:C3)=(A1&B1&C1))) Typo. that should have been =1+SUMPRODUCT(--((A2:A3&B2:B3&C2:C3)=(A1&B1&C1))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding contents of one cell to a range of cells. | Excel Worksheet Functions | |||
Function to determine if any cell in a range is contained in a given cell | Excel Worksheet Functions | |||
Paste rows of numbers from Word into single Excel cell | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions | |||
Returning a Value to a Cell Based on a Range of Uncertain Size | Excel Worksheet Functions |