occurance of numbers in cell range

[Dillenger]

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.

 

[Domenic]

Try the following...

=SUM(COUNTIF(A1:C3,{3,11,54}))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Dillenger]

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.

 

[RagDyeR]

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!
==============================================


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.

 

[RagDyeR]

BTW,
*Not* really an array formula.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Try the following...

=SUM(COUNTIF(A1:C3,{3,11,54}))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Ola]

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)"

 

[Ola]

....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

 

[Dillenger]

This is exactly what I need. Thank you all for your help

 

[Ola]

Thanks for the feedback
Ola

 

[Domenic]

BTW,
*Not* really an array formula.

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

Cheers!

 

[Harlan Grove]

...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))

 

[Myrna Larson]

a classic bug in waiting

ROFL! I like that!

 

[Ola]

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

 

[Harlan Grove]

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)))

 

[Harlan Grove]

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)))