Count Unique value with multiple conditon

[EC_08]

Hi,

My data is look like following.. in column A i have different date , In clolumn B :Different Name , Column C : Diff Grade(A,B,C)

Date	Name	Grade
26-Aug	Jack	A
26-Aug	Muly	B
26-Aug	Desely	C
26-Aug	Vizy	C
26-Aug	Unit	A
26-Aug	Muly	A
26-Aug	Desely	B
26-Aug	Vizy	C
26-Aug	Jack	A
26-Aug	Jack	B
26-Aug	Muly	B
26-Aug	Desely	B
26-Aug	Vizy	C

<tbody>
</tbody>

I want Unique count Output As following..

Grade	26-Aug
A	3	3 Is unique name in Column B that contain Grade A on 26th Aug
B	3	3 Is unique name in Column B that contain Grade B on 26th Aug
C	2	2 Is unique name in Column B that contain Grade C on 26th Aug

<tbody>
</tbody>

***This is only example i have different date 1 to 30 in column A , lots of name in Column B & 9 different grade in Column C. & Output also date wise(Like 26th , 27th,28th .... .

 

[Aladin Akyurek]

Let A:C house the data and E:F the processing...

F2, control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($C$2:$C$20=$E2,IF($B$2:$B$20<>"",
  IF($C$2:$C$20=E$1,MATCH($B$2:$B$20,$B$2:$B$20,0)))),
  ROW($B$2:$B$20)-ROW($B$2)+1),1))

 

[Andrew Poulsom]

I think it should be:

=SUM(IF(FREQUENCY(IF($C$2:$C$20=$E2,IF($B$2:$B$20<>"",
IF($A$2:$A$20=F$1,MATCH($B$2:$B$20,$B$2:$B$20,0)))),
ROW($B$2:$B$20)-ROW($B$2)+1),1))

 

[Aladin Akyurek]

I think it should be:

=SUM(IF(FREQUENCY(IF($C$2:$C$20=$E2,IF($B$2:$B$20<>"",
IF($A$2:$A$20=F$1,MATCH($B$2:$B$20,$B$2:$B$20,0)))),
ROW($B$2:$B$20)-ROW($B$2)+1),1))

Yes that's right! Thanks for the catch.

 

[EC_08]

Let A:C house the data and E:F the processing...

F2, control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($C$2:$C$20=$E2,IF($B$2:$B$20<>"",
  IF($C$2:$C$20=E$1,MATCH($B$2:$B$20,$B$2:$B$20,0)))),
  ROW($B$2:$B$20)-ROW($B$2)+1),1))

Thanks Its working..

Only one thing here we use array function if my data is large then excel take much time to load ....
any other formula that not contain array.

 

[James006]

Hi,

You do need an array formula ...
May be without using Frequency, it is lighter ...

=SUM(IF($C$2:$C$20=$E2,IF($B$2:$B$20<>"",IF($A$2:$A$20=F$1,IF(MATCH($A$2:$A$14&$B$2:$B$14&$C$2:$C$14,$A$2:$A$14&$B$2:$B$14&$C$2:$C$14,0)=ROW($A$2:$A$14)-ROW($A$2)+1,1)))))

HTH

 

[Aladin Akyurek]

Thanks Its working..

Only one thing here we use array function if my data is large then excel take much time to load ....
any other formula that not contain array.

Concatenating the relevant fields would alleviate the performance problem a bit.

D2, copied down:

=$A2&"|"&$B2&"|"&C2

Assuming that the data is in Sheet1, define Ivec by means of Formulas | Name Manager as referring to:

=ROW(Sheet1!$A$2:$A$20)-ROW(Sheet1!$A$2)+1

Now invoke...

F2, control+shift=enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(F$1&"|*"&$E2,$D$2:$D$20)),
  MATCH($D$2:$D$20,$D$2:$D$20,0)),Ivec),1))