number occurrences from multiple cells.

[mythology2]

Hi,

I have multiple occurrences of numbers form 1 to 49 between cells A1 and G92. I also have in column K the numbers 1 to 49, one number in each cell.
What I want to do is count the number of times the numbers in cells A1 through to G92 occur and place that number next to it's corresponding number in column K.

I've tried the formula: =SUM(IF(A1:G92=1,1,0)) in the cell next to the number 1 in column K to calculate how many times the number 1 occurs.

I get a return of 0, where i can quite clearly see that the number 1 occurs many times.

This formula I got from the Microsoft website excel help page.

 

[mythology2]

Sorry, all that garbled text was a .PNG I dragged in. Can't see anywhere to edit post.

 

[James006]

Hello,

You are using an Array Formula ...

=SUM(IF(A1:G92=1,1,0))

So you should use simultaneously the 3 keys Control+Shift+Enter ... instead of the Enter Key ...

HTH

 

[mythology2]

Hello,

You are using an Array Formula ...

=SUM(IF(A1:G92=1,1,0))

So you should use simultaneously the 3 keys Control+Shift+Enter ... instead of the Enter Key ...

HTH

'
thank you, that's not explained very well. I had assumed you pressed the 3 keys BEFORE entering the formula to get into 'array mode'

 

[James006]

'
thank you, that's not explained very well. I had assumed you pressed the 3 keys BEFORE entering the formula to get into 'array mode'

No ...

1. You type your formula ...

2. and ... Instead of the standard Enter key ... you have to use the 3 keys simultaneously ...

 

[Aladin Akyurek]

Is it not just...

=COUNTIFS($A$1:$G$92,$K1)

entered in L1 and copied down?

 

[Aladin Akyurek]

Also, in L1 entered and copied down:

=INDEX(FREQUENCY($A$1:$G$92,CHOOSE({1,2},$K1-1,$K1)),2)

which is equally effective, maybe even faster.