Need Unique Value Count with More Criteria (Please Help Me)

himadri Chakraborty

New Member
Joined
Nov 8, 2017
Messages
2
[TABLE="width: 800"]
<colgroup><col span="10"><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]State[/TD]
[TD]Site_ID[/TD]
[TD]CAT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Formula_Needed Below[/TD]
[/TR]
[TR]
[TD="align: right"]01-Dec[/TD]
[TD]ASSAM[/TD]
[TD]UBGVR1[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DATE[/TD]
[TD]CAT[/TD]
[TD]State[/TD]
[TD]Unique_Site_Count[/TD]
[/TR]
[TR]
[TD="align: right"]01-Dec[/TD]
[TD]NE[/TD]
[TD]UBGVR1[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Dec[/TD]
[TD]Fiber[/TD]
[TD]ASSAM[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]01-Dec[/TD]
[TD]NE[/TD]
[TD]UBGVR1[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02-Dec[/TD]
[TD]Active[/TD]
[TD]NE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Dec[/TD]
[TD]NE[/TD]
[TD]UKRIS1[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Dec[/TD]
[TD]NE[/TD]
[TD]UPKAN1[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Dec[/TD]
[TD]NE[/TD]
[TD]UKRIS1[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Dec[/TD]
[TD]NE[/TD]
[TD]UDLGAR[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Dec[/TD]
[TD]NE[/TD]
[TD]UKRIS1[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Dec[/TD]
[TD]NE[/TD]
[TD]UDLGAR[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Dec[/TD]
[TD]NE[/TD]
[TD]UPKAN1[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Dec[/TD]
[TD]NE[/TD]
[TD]UDLGAR[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Dec[/TD]
[TD]NE[/TD]
[TD]UBENB1[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Dec[/TD]
[TD]NE[/TD]
[TD]UBENB1[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Dec[/TD]
[TD]NE[/TD]
[TD]UBENB1[/TD]
[TD]Passive[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Dec[/TD]
[TD]ASSAM[/TD]
[TD]UNAGS1[/TD]
[TD]Passive[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Dec[/TD]
[TD]ASSAM[/TD]
[TD]UNAGS1[/TD]
[TD]Passive[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Dec[/TD]
[TD]ASSAM[/TD]
[TD]UNAGS1[/TD]
[TD]Passive[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Dec[/TD]
[TD]ASSAM[/TD]
[TD]UBRG1R[/TD]
[TD]Passive[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Dec[/TD]
[TD]ASSAM[/TD]
[TD]UBRG1R[/TD]
[TD]Passive[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Dec[/TD]
[TD]ASSAM[/TD]
[TD]UBRG1R[/TD]
[TD]Passive[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Dec[/TD]
[TD]ASSAM[/TD]
[TD]U52005[/TD]
[TD]Passive[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Dec[/TD]
[TD]ASSAM[/TD]
[TD]U52005[/TD]
[TD]Passive[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Dec[/TD]
[TD]ASSAM[/TD]
[TD]U52005[/TD]
[TD]Passive[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Dec[/TD]
[TD]ASSAM[/TD]
[TD]UDOM01[/TD]
[TD]Passive[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Dec[/TD]
[TD]ASSAM[/TD]
[TD]UGING1[/TD]
[TD]Passive[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Dec[/TD]
[TD]ASSAM[/TD]
[TD]UGING1[/TD]
[TD]Passive[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Dec[/TD]
[TD]ASSAM[/TD]
[TD]UDOM01[/TD]
[TD]Passive[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Dec[/TD]
[TD]ASSAM[/TD]
[TD]UGING1[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Dec[/TD]
[TD]ASSAM[/TD]
[TD]UDOM01[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Dec[/TD]
[TD]ASSAM[/TD]
[TD]UDOM01[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Dec[/TD]
[TD]NE[/TD]
[TD]UBEHA1[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Dec[/TD]
[TD]NE[/TD]
[TD]UBEHA1[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Dec[/TD]
[TD]NE[/TD]
[TD]U52004[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Dec[/TD]
[TD]NE[/TD]
[TD]U52004[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Dec[/TD]
[TD]NE[/TD]
[TD]UBDTT1[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Dec[/TD]
[TD]NE[/TD]
[TD]UBDTT1[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Dec[/TD]
[TD]NE[/TD]
[TD]UBDTT1[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Dec[/TD]
[TD]NE[/TD]
[TD]UNRK01[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Dec[/TD]
[TD]NE[/TD]
[TD]UNRK01[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Dec[/TD]
[TD]NE[/TD]
[TD]UNRK01[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Dec[/TD]
[TD]NE[/TD]
[TD]UKUTK1[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Dec[/TD]
[TD]NE[/TD]
[TD]UKUTK1[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Dec[/TD]
[TD]ASSAM[/TD]
[TD]UKUTK1[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Dec[/TD]
[TD]ASSAM[/TD]
[TD]U52007[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Dec[/TD]
[TD]ASSAM[/TD]
[TD]U52007[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Dec[/TD]
[TD]ASSAM[/TD]
[TD]UNRNP1[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Dec[/TD]
[TD]ASSAM[/TD]
[TD]UNRNP1[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Dec[/TD]
[TD]ASSAM[/TD]
[TD]UNRNP1[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Dec[/TD]
[TD]ASSAM[/TD]
[TD]UTN009[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Dec[/TD]
[TD]ASSAM[/TD]
[TD]UTN009[/TD]
[TD]Fiber[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Let's assume the following...

A1:K52

[TABLE="width: 950"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Date[/TD]
[TD="class: xl63, width: 64"]State[/TD]
[TD="class: xl63, width: 64"]Site_ID[/TD]
[TD="class: xl63, width: 64"]CAT[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]Formula_Needed Below[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]01-Dec[/TD]
[TD="class: xl63"]ASSAM[/TD]
[TD="class: xl63"]UBGVR1[/TD]
[TD="class: xl63"]Fiber[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]DATE[/TD]
[TD="class: xl63"]CAT[/TD]
[TD="class: xl63"]State[/TD]
[TD="class: xl63"]Unique_Site_Count[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]01-Dec[/TD]
[TD="class: xl63"]NE[/TD]
[TD="class: xl63"]UBGVR1[/TD]
[TD="class: xl63"]Active[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl64, align: right"]01-Dec[/TD]
[TD="class: xl63"]Fiber[/TD]
[TD="class: xl63"]ASSAM[/TD]
[TD="class: xl63, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]01-Dec[/TD]
[TD="class: xl63"]NE[/TD]
[TD="class: xl63"]UBGVR1[/TD]
[TD="class: xl63"]Active[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl64, align: right"]02-Dec[/TD]
[TD="class: xl63"]Active[/TD]
[TD="class: xl63"]NE[/TD]
[TD="class: xl63, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]01-Dec[/TD]
[TD="class: xl63"]NE[/TD]
[TD="class: xl63"]UKRIS1[/TD]
[TD="class: xl63"]Active[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]01-Dec[/TD]
[TD="class: xl63"]NE[/TD]
[TD="class: xl63"]UPKAN1[/TD]
[TD="class: xl63"]Active[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]01-Dec[/TD]
[TD="class: xl63"]NE[/TD]
[TD="class: xl63"]UKRIS1[/TD]
[TD="class: xl63"]Active[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]01-Dec[/TD]
[TD="class: xl63"]NE[/TD]
[TD="class: xl63"]UDLGAR[/TD]
[TD="class: xl63"]Active[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]01-Dec[/TD]
[TD="class: xl63"]NE[/TD]
[TD="class: xl63"]UKRIS1[/TD]
[TD="class: xl63"]Active[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]etc......[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]


[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]

Then, enter the following formula in K3, confirm with CONTROL+SHIFT+ENTER, and copy down:

Code:
=SUM(IF(FREQUENCY(IF($A$2:$A$52=$H3,IF($D$2:$D$52=$I3,IF($B$2:$B$52=$J3,IF($C$2:$C$52<>"",MATCH($C$2:$C$52,$C$2:$C$52,0))))),ROW($C$2:$C$52)-ROW($C$2)+1)>0,1))

Hope this helps!
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top