Count unique entries across multiple columns

[Bob Phillips]

=SUMPRODUCT(--($A$2:$A$20=5490),--($C$2:$C$20=100),--($D$2:$D$20=34))

etc.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[dan dungan]

I would put this formula in Column E

=A2&C2&D2

and then run a pivot table.

 

[medirate]

I am trying to count the number of unique entries over a group of columns.
For instance, I have the following in a spreadsheet:


A
B
C
D

1
Account
User
Fund
Dept

2
5490
Sam
100
34

3
5490
Sam
100
34

4
5490
Sam
100
35

5
5491
Ted
200
34

6
5491
Fred
200
34



I am interested in the Account, Fund, and Dept columns which, when combined,
form a unique entry. So I need the unique entry and its count. For instance:

5490 100 34 (2)
5490 100 35 (1)
5491 200 34 (2)

Can anyone help?