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



Thanks,
Imran.

[cbatrody]

Hi,

Welcome to the forum.

Can you attach a sample workbook (in excel format) with enough data to demonstrate your requirement? Make sure your desired results are shown, mock them up manually if necessary. Remember to desensitize the file by removing all confidential information before upload!

Click on "Go Advanced" -> "Mange Attachments" option to upload the file.

[imran1059]

The file is uploaded

[cbatrody]

Modify the formula in E2 as following:

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

drag it down the cells.