Distinct Count

[Szucchari]

Hi,
I need a formula that will do a distinct count for multiple criteria. I need the Rows to only be counted once if Rows A, B, and C are all distinct. This examples is small but the workbook I have to do this distinct count on is over 36,000 rows.

Customer Account number	Customer First name	Customer Last name	Item number
555555	Lee	Adams	103
222222	Mike	Jones	103
88888	Steph	Tyme	103
444444	Holly	Moore	103
555555	Lee	Adams	103
222222	Mike	Jones	103
444444	Holly	Moore	103
7777777	Steph	Tamen	103

 

[ravi2628]

Hi,
I need a formula that will do a distinct count for multiple criteria. I need the Rows to only be counted once if Rows A, B, and C are all distinct. This examples is small but the workbook I have to do this distinct count on is over 36,000 rows.

Customer Account number	Customer First name	Customer Last name	Item number
555555	Lee	Adams	103
222222	Mike	Jones	103
88888	Steph	Tyme	103
444444	Holly	Moore	103
555555	Lee	Adams	103
222222	Mike	Jones	103
444444	Holly	Moore	103
7777777	Steph	Tamen	103

HI,

Please try this formula.

=ROWS(UNIQUE(A2:C9,0,0))


Please update your Excel Version in the profile so that we can provide accurate formula.

 

[maabadi]

If you have other version of excel, Try this:

=SUMPRODUCT(1/COUNTIFS($A$2:$A$20,$A$2:$A$20,$B$2:$B$20,$B$2:$B$20,$C$2:$C$20,$C$2:$C$20))