Im new to excel and need help please

[allday34]

Ok I will do my best to explain this clearly.

I have 4 columns and 3 rows that will have a number in them. No number in a column will be the same but numbers in the rows can be the same. I need a box to automatically tell me the if the numbers are the same.

Example
A1 - 3 B1 - 5 C1 - 5 D1 - 4
A2 - 5 B2 - 3 C2 - 1 D2 - 7
A3 - 1 B3 - 6 C3 - 3 D3 - 5

So I want a box to say that 5 is in all of them.
Another box to say 3 is in 3 of them.
And another 1 that says 1 is in 2 of them.

Can someone help me out if this is possible?

 

[Von Pookie]

Assuming you have the ability to add a table to the sheet, place the numbers 0-9 down column A (ie. A5=0, A6=1, etc.)

Then in B5, use
=SUM(COUNTIF($A$1:$A$3,$A5),COUNTIF($B$1:$B$3,$A5),COUNTIF($C$1:$C$3,$A5),COUNTIF($D$1:$D$3,$A5))

and copy the formula down. The number in column B will be the number of times the number in column A is used in the *columns*.

It is technically a terrible formula, but does give the desired results for the time being, until someone else can make a "prettier" one.

 

[Dave Patton]

Try =COUNTIF($A$1:$D$3,A5) and fill or copy down.

Does this formula give the correct results?

 

[Von Pookie]

Dave,

my understanding of the OP was that it needs to count by column, not just the total of each number for the entire range.

I could be wrong, too. It was a little weird to wrap my head around.

 

[jasonb75]

How about

Excel Workbook
	A	B	C	D
1	3	5	5	4
2	5	3	1	7
3	1	6	3	5
4
5	3 (x3)	5 (x4)	5 (x4)	4 (x1)
6	5 (x4)	3 (x3)	1 (x2)	7 (x1)
7	1 (x2)	6 (x1)	3 (x3)	5 (x4)
Sheet2

cell value (x count of occurences)

Drag your mouse over a 4 col x 3 row range of empty cells, press F2, paste this formula

=A1:D3&" (x"&COUNTIF(A1:D3,"="&A1:D3)&")"

Ctrl + Shift + Enter to enter it as an array formula.

Hope this helps