Modify formula in Column E to exclude recurring customer names in column A.

[imran1059]

Hi Everone,

I have following formula in E2

=COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2,$D$2:$D2,D2,$C$2:$C2,C2)

and its giving me following output. I need to count unique customers in given criteria. Right Most column is my desired output.

[TABLE="width: 677"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Customer Name[/TD]
[TD]Region[/TD]
[TD]Division[/TD]
[TD]Category[/TD]
[TD]Sr. No. of Unique Customers per Region per Division per Category
(Output by Formula)[/TD]
[TD]Sr. No. of Unique Customers per Region per Division per Category
(Desired Output)[/TD]
[/TR]
[TR]
[TD="align: left"]ABC Communication[/TD]
[TD]Central[/TD]
[TD]CND[/TD]
[TD]Private[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD="align: left"]ABC Communication[/TD]
[TD]Central[/TD]
[TD]CND[/TD]
[TD]Government[/TD]
[TD]1.00[/TD]
[TD]0.50[/TD]
[/TR]
[TR]
[TD="align: left"]ABC Communication[/TD]
[TD]Central[/TD]
[TD]CND[/TD]
[TD]Semi-Government[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD="align: left"]ABC Communication[/TD]
[TD]Western[/TD]
[TD]CND[/TD]
[TD]Semi-Government[/TD]
[TD]1.00[/TD]
[TD]0.33[/TD]
[/TR]
[TR]
[TD="align: left"]ABC Communication[/TD]
[TD]Western[/TD]
[TD]CND[/TD]
[TD]Semi-Government[/TD]
[TD]2.00[/TD]
[TD]0.33[/TD]
[/TR]
[TR]
[TD="align: left"]ABC Communication[/TD]
[TD]Western[/TD]
[TD]CND[/TD]
[TD]Semi-Government[/TD]
[TD]3.00[/TD]
[TD]0.33[/TD]
[/TR]
[TR]
[TD="align: left"]ABC Communication[/TD]
[TD]Western[/TD]
[TD]END[/TD]
[TD]Semi-Government[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD="align: left"]ABC Communication[/TD]
[TD]Western[/TD]
[TD]CND[/TD]
[TD]Government[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD="align: left"]ABC Communication[/TD]
[TD]Central[/TD]
[TD]CND[/TD]
[TD]Government[/TD]
[TD]2.00[/TD]
[TD]0.50[/TD]
[/TR]
[TR]
[TD="align: left"]ABC Communication[/TD]
[TD]Central[/TD]
[TD]Multi[/TD]
[TD]Government[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD="align: left"]United Company[/TD]
[TD]Eastern[/TD]
[TD]CND[/TD]
[TD]Private[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD="align: left"]United Company[/TD]
[TD]Western[/TD]
[TD]BBT[/TD]
[TD]Private[/TD]
[TD]1.00[/TD]
[TD]0.50[/TD]
[/TR]
[TR]
[TD="align: left"]United Company[/TD]
[TD]Western[/TD]
[TD]BBT[/TD]
[TD]Private[/TD]
[TD]2.00[/TD]
[TD]0.50[/TD]
[/TR]
[TR]
[TD="align: left"]United Company[/TD]
[TD]Central[/TD]
[TD]BBT[/TD]
[TD]Private[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD="align: left"]United Company[/TD]
[TD]Central[/TD]
[TD]CKQ[/TD]
[TD]Private[/TD]
[TD]1.00[/TD]
[TD]0.25[/TD]
[/TR]
[TR]
[TD="align: left"]United Company[/TD]
[TD]Central[/TD]
[TD]CKQ[/TD]
[TD]Private[/TD]
[TD]2.00[/TD]
[TD]0.25[/TD]
[/TR]
[TR]
[TD="align: left"]United Company[/TD]
[TD]Central[/TD]
[TD]CKQ[/TD]
[TD]Private[/TD]
[TD]3.00[/TD]
[TD]0.25[/TD]
[/TR]
[TR]
[TD="align: left"]United Company[/TD]
[TD]Central[/TD]
[TD]CKQ[/TD]
[TD]Private[/TD]
[TD]4.00[/TD]
[TD]0.25[/TD]
[/TR]
</tbody>[/TABLE]


Thanks,
Imran.

 

[Kaper]

I've displayed it (as one severa of no-reply-post in separate window to reply one-by-one), and was just about to answer, but resigned after seeing your PM breaking rule 4 of https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[imran1059]

Its okay .... I didn't know about this rule. Accept my apologies and just cheer will take care from now on.

 

[imran1059]

For the reference of readers ....... I have got my desired output using following formula in Cell F2

=SUM(IF(COUNTIFS(A:A,A2,B:B,B2,C:C,C2,D:D,D2),1/COUNTIFS(A:A,A2,B:B,B2,C:C,C2,D:D,D2)))

 

[Kaper]

Glad to see you posted the solution for those who find this thread.