Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Dillenger
 
Posts: n/a
Default 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   Report Post  
Domenic
 
Posts: n/a
Default

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   Report Post  
Dillenger
 
Posts: n/a
Default

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   Report Post  
RagDyeR
 
Posts: n/a
Default

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   Report Post  
RagDyeR
 
Posts: n/a
Default

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   Report Post  
Ola
 
Posts: n/a
Default

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   Report Post  
Ola
 
Posts: n/a
Default

....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   Report Post  
Dillenger
 
Posts: n/a
Default

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   Report Post  
Ola
 
Posts: n/a
Default

Thanks for the feedback
Ola
  #10   Report Post  
Domenic
 
Posts: n/a
Default

In article ,
"RagDyeR" wrote:

BTW,
*Not* really an array formula.


Thanks for catching that and bringing it to my attention. Much
appreciated.

Cheers!


  #11   Report Post  
Harlan Grove
 
Posts: n/a
Default

"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   Report Post  
Myrna Larson
 
Posts: n/a
Default

On Sat, 19 Feb 2005 17:38:13 -0800, "Harlan Grove" wrote:

a classic bug in waiting


ROFL! I like that!

  #13   Report Post  
Ola
 
Posts: n/a
Default

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   Report Post  
Harlan Grove
 
Posts: n/a
Default

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   Report Post  
Harlan Grove
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding contents of one cell to a range of cells. CLJinVA Excel Worksheet Functions 1 February 10th 05 11:19 PM
Function to determine if any cell in a range is contained in a given cell [email protected] Excel Worksheet Functions 3 February 7th 05 05:19 PM
Paste rows of numbers from Word into single Excel cell BecG Excel Discussion (Misc queries) 1 December 8th 04 05:55 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM
Returning a Value to a Cell Based on a Range of Uncertain Size amc422 Excel Worksheet Functions 7 November 14th 04 04:03 PM


All times are GMT +1. The time now is 05:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"