I need to calculate how many times a number appears on the sheet

I need some help with a formula to calculate (automatically) how many times a number appears on a sheet I have 1-20 and want to know if there is a formula that can calculate how many times each one appeared in the sheet I have included the sheet I am working from If you need more info please ask as I am desperate

Thank you
Johann

6 4 2
2 1 15
1 4 5 How many times
5 1 2 1 ?
1 11 2 2 ?
7 2 3 3 ?
2 1 5 4 ?
9 3 16 5 ?
6 ?
6 9 11 7 ?
4 11 12 8 ?
7 6 8 9 ?
7 5 2 10 ?
10 8 15 11 ?
4 3 1 12 ?
13 ?
12 5 2 14 ?
1 2 4 15 ?
3 6 2 16 ?
17 ?
1 9 10 18 ?
5 7 1 19 ?
10 3 2 20 ?
11 1 9

1 2 8
1 2 10
1 2 10
8 3 4
3 7 1
2 3 6

9 3 5
2 1 12
2 13 9
7 1 2
7 10 1
7 1 6

1 9 12
2 10 1



8 4 7
2 7 6
5 14 8

11 5 3

1 3 8

16 6 10
6 3 1

6 8 3
1 2 18

2 18 11
7 4 6
8 1 7
10 5 2
15 2 4
19 14 20
4 1 10
7 4 6
4 1 13

9 12 1
5 9 7
3 10 1
1 3 7
6 1 3
8 1 9
7 1 3
7 5 3

>�
p<
�@�td class=xl69 style='border-top:none;border-left:none'>

use countif or countifs.

Thank you for the reply I must however confess that I am a newby in excel and do not know how to use it here :-(

Hi Richard thank you Yes they are within the rages you mentioned but when I run the formula you gave it returns 0 where do I tell the formula to look for the number?

Originally Posted by Richard Buttrey

Copy the table of data you mentioned into A1:D76,
Put the numbers 1-20 in H1:H20
Put the formula I gave you in I1 and copy it down.

See attached. Otherwise upload your actual workbook

THank you Richard your attached file worked like a dream I than you again and have a great 2015

Originally Posted by Richard Buttrey

Copy the table of data you mentioned into A1:D76,
Put the numbers 1-20 in H1:H20
Put the formula I gave you in I1 and copy it down.

See attached. Otherwise upload your actual workbook

Hi Richard Me again I have another question on this same sheet. I now have the following numbers 1,2 3, 7, 11 Is there a formula for me to see when any of these numbers appear in a line and tell me how many of the numbers is in a specific line , it does not have to tell me which number appeared but how many of them appeared. It has to give me a result line by line? Thanking you in advance JOhann

Originally Posted by Richard Buttrey

Hi,

The simplest way would be on row 1 copied down

Formula:

Please Login or Register  to view this content.

Again thank you..

one more silly question to pick your brain with say I have some cells filled with a color...say yellow. is there a way to count the yellow cells? I know I am pushing my luck here :-)

If you want to go line by line, a shorter formula would be:

Formula:

Please Login or Register  to view this content.

If you want to count how many are on your sheet total:

Formula:

Please Login or Register  to view this content.

I don't know of a formula that will count conditionally formatted cells or cells that are just filled with color, but you could probably do it with VBA.

thank you :-)