match and Count value but not repeat the same ..

**amruta_shah15** · 07-26-2014, 04:10 AM

Dear Friends,

I come up with a query regarding for excel formula.. hope you will help me in this....

In attached File, there is a table there is date in COLUMN "G" and column "H" contain Name....

I want to count value in column "L" by matching date from column " K " .....BUT THE SAME NAME SHOULD NOT BE REPEAT COUNT... i.e, HITENDRA and RITU name is repeat 2 times in date 1-Jul-2014 .. but it is a similar so it should count as a "1"..... the total count for the day is "6" but avoiding similar value the total count is "4"..... so I nead a "4" figure as a result......

Kindly find the attachment for further understanding ..

Looking to hear from you soon....

**Punnam** · 07-26-2014, 04:19 AM

HI,

attachment not found ?
Re attachment it.

Punnam

**Tony Valko** · 07-26-2014, 09:50 AM

Try something like this...

Data Range

	A	B	C	D	E
1	Date	Name	------	Date	Count
2	7/26/2014	Pam		7/26/2014	5
3	7/26/2014	Mam
4	7/26/2014	Bam
5	7/26/2014	Pam
6	7/26/2014	Bam
7	7/26/2014	Tam
8	7/26/2014	Tam
9	7/26/2014	Tam
10	7/26/2014	Sam

This array formula** entered in E2:

=SUM(IF(FREQUENCY(IF(A2:A10=D2,MATCH(B2:B10,B2:B10,0)),ROW(B2:B10)-ROW(B2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.