# Im new to excel and need help please

#### allday34

##### New Member
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?

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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.

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

Does this formula give the correct results?

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.

Excel Workbook
ABCD
13554
25317
31635
4
53 (x3)5 (x4)5 (x4)4 (x1)
65 (x4)3 (x3)1 (x2)7 (x1)
71 (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

Replies
2
Views
175
Replies
1
Views
94
Replies
22
Views
365
Replies
16
Views
727
Replies
1
Views
88

1,207,097
Messages
6,076,556
Members
446,213
Latest member
bettigb

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back