Counting how often numbers in a range occur in rows of data

I have rows and rows of numbers and I'd like excel to count how many times numbers occur in the 40s, 30s, 20s and 10s

Example:
Row 1: 10 11 32 38 41 44
Row 2: 12 17 23 31 43 46
Row 3: 15 18 23 29 32 43

For the 40s:
The number of times a number occurs twice in the 40s is 2

the number of times a number occurs once in the 40s is 1

the number of times a number appears three times in the 40s is 0


For the 30s:
The number of times a 30s number appears once is 2

The number of times a 30s number appears twice is 1,

The number of times a number in the 30s appears three times or not at all in a row is 0


so as i have 6 columns of numbers, i'd like it to calculate how often numbers in the 40s, 30s, 20s, and 10s appear once, twice, three times, four times, five times, and six times in each row
Does anyone have any idea what formula I could use? Any help would really be appreciated.

Yes, I'd like excel to count in all the rows how often numbers in the 40s, 30s, 20s, etc occur once, twice, three, four, five and six times per row and then total them and produce a summary table with the once, twice, three times totals.

So in the example:

Row 1: 10 11 32 38 41 44
Row 2: 12 17 23 31 43 46
Row 3: 15 18 23 29 32 43

The number of times a number in the 40s only occurs once in all rows displayed is 1 (Row 3)

The number of times a number in the 40s occurs twice in all the rows is 2 (once in Row 1 and once in Row 2)

The number of times numbers in the 40s occurs three, four, five, or six times (so far) is 0.

I could manually count them and produce a table like so:

0x 1x 2x 3x 4x 5x 6x
40s 0 1 2 0 0 0 0
30s 0 2 1 0 0 0 0
20s 1 1 1 0 0 0 0
10s 0 0 3 0 0 0 0
0s 0 0 0 0 0 0 0


The problem is there are over 1000 rows and it's a growing list. What formula or formulas would I need to employ to get Excel to produce such a summary table.

Thanks.

Thank you Marcol. I will take a look at that sheet you provided

Possibly, but I read

in the 40s

to be 40 to 49

Maybe not...

That's right. Sorry I should have given a better range. 40s means how often numbers from 40 to 49 occur in all the rows. 30s is 30 to 39, etc.

Yes, Ron has provided the exact solution I'm looking for and in an amazingly short time. I'm going to look at it now and see if I can understand how it works. I love learning new formulas and functions I never knew existed

In the formula, say in I2:

{=SUM(--(FREQUENCY(IF((INT($A$1:$F$15/10)*10=$H2),ROW($A$1:$F$15)),ROW($A$1:$A$16)-1)=I$1))}

Could you explain why reference $A$1:$F$15 is divided by 10 (hopefully highlighted in bold in the quote above)?


Many thanks guys.

In other words, Ron: genius! I would never have even thought that way let alone been able to actually formulate it. So a huge thank you.

I've tried to extend it to also count how often no numbers, say, from 20 to 29 occur in the rows. So some rows read:

Row 1: 10 11 32 38 41 44 (contains no integer from 20 to 29)
Row 4: 10 11 32 38 41 44 (also contains no integer from 20 to 29)

and none of the rows contain any numbers from 1 to 9, i.e. 15 rows contain no numbers from 1 to 9.

If the results of these "absences" were in column L (L1 = 0), they should total up as follows:

___L
___0
40 0
30 0
20 5
10 0
0 15


If I change the end of the formula to L$1 where L1 = 0, in the rows of data you provided, it counts the "absences" as:

40 2
30 2
20 7
10 2
0 17 !

Using the formula:

=SUM(--(FREQUENCY(IF((INT($A$1:$F$15/10)*10=$H6),ROW($A$1:$F$15)),ROW($A$1:$A$16)-1)=L$1))

How do I then adjust the formula because now I'm asking excel to count the absence of a range in each of the rows?

Is it possible?

Many thanks.

Thanks Marcol for the alternative version. Having two routes means I can use one route to check on the other, so it's very much appreciated

Hi Ron Coderre

I've been fighting all day with this thread to try to understand it better!

I think geekalpha might be looking for 1>9 instead of 0>9 and then just 0

The 0 bit is no problem

Please Login or Register  to view this content.

It's the 1>9 that has me beaten.

(This might also apply to the formula you just posted.)

I can get to result easily with my rather basic method, but I would like to complete the set before posting a combined demo workbook.

Would you mind helping out?

Spot on Ron

I've taken the liberty of comparing my rather primitive method to yours in the attached workbook.

All credit to you.

I hope it helps geekalpha as much as it has helped me.

Thanks again.

Thank you everyone for the great support.

Marcol is right that I want to count occurences and absences of numbers in the following groups:

1 - 9
10 - 19
20 - 29
30 - 39
40 - 49

Sorry I didn't outline it like that but it's a growing operation so putting it into words wasn't easy. Ron Codorre seems to have provided the solutions so I'll take a look at the formulas and try to learn how they work, and let you know how I get on.

Thanks again