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.

Customer Name	Region	Division	Category	Sr. No. of Unique Customers per Region per Division per Category (Output by Formula)	Sr. No. of Unique Customers per Region per Division per Category (Desired Output)
ABC Communication	Central	CND	Private	1.00	1.00
ABC Communication	Central	CND	Government	1.00	0.50
ABC Communication	Central	CND	Semi-Government	1.00	1.00
ABC Communication	Western	CND	Semi-Government	1.00	0.33
ABC Communication	Western	CND	Semi-Government	2.00	0.33
ABC Communication	Western	CND	Semi-Government	3.00	0.33
ABC Communication	Western	END	Semi-Government	1.00	1.00
ABC Communication	Western	CND	Government	1.00	1.00
ABC Communication	Central	CND	Government	2.00	0.50
ABC Communication	Central	Multi	Government	1.00	1.00
United Company	Eastern	CND	Private	1.00	1.00
United Company	Western	BBT	Private	1.00	0.50
United Company	Western	BBT	Private	2.00	0.50
United Company	Central	BBT	Private	1.00	1.00
United Company	Central	CKQ	Private	1.00	0.25
United Company	Central	CKQ	Private	2.00	0.25
United Company	Central	CKQ	Private	3.00	0.25
United Company	Central	CKQ	Private	4.00	0.25

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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.