# Count Unique value with multiple conditon

#### EC_08

##### New Member
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 .... .

Last edited:

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Let A:C house the data and E:F the processing...

F2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````=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))
``````

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))

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.

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

F2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````=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.

Hi,

You do need an array formula ...
May be without using Frequency, it is lighter ...
Code:
``=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

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:
Rich (BB code):
``````=\$A2&"|"&\$B2&"|"&C2
``````

Assuming that the data is in Sheet1, define Ivec by means of Formulas | Name Manager as referring to:
Rich (BB code):
``=ROW(Sheet1!\$A\$2:\$A\$20)-ROW(Sheet1!\$A\$2)+1``
Now invoke...

F2, control+shift=enter, not just enter, and copy down:
Rich (BB code):
``````=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))
``````

Replies
1
Views
166
Replies
5
Views
206
Replies
6
Views
392
Replies
15
Views
654
Replies
2
Views
215

1,221,418
Messages
6,159,791
Members
451,589
Latest member
Harold14

### 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.

### Which adblocker are you using?

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

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