Conditional Count Related Formula Help

xlmaniac

Well-known Member
Joined
Jul 2, 2009
Messages
531
Office Version
  1. 2010
Platform
  1. Windows
Dear All,
I do have the following data set across A5:E30.
I would like to find out the unique count of styles that has been GRNed.
The formula needs to check in column E whether the cell is filled in with the GRN number or not. If the GRN number is there and then the unique count of styles against the GRN numbers needs to get populated under the summary section in cel lE2.
My desired result based on the below data set is 4.
I am unable to find out the correct path.
Could somebody help in fixing this out pls?
Sheet1

*ABCDE
*
*
*****
*****
*
*
*
*
*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 74px;"><col style="width: 74px;"><col style="width: 95px;"><col style="width: 191px;"><col style="width: 152px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="align: center"]Department[/TD]
[TD="align: center"]Category[/TD]
[TD="align: center"]Sub Category[/TD]
[TD="bgcolor: #FFFF00, align: center"]# Style(Desired Result)[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="bgcolor: #FFFF00, align: center"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: center"]Department[/TD]
[TD="align: center"]Category[/TD]
[TD="align: center"]Sub Category[/TD]
[TD="align: center"]Style Name[/TD]
[TD="align: center"]GRN No[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Mercer County Mustard Chinos[/TD]
[TD="align: center"]12240[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Mercer County Mustard Chinos[/TD]
[TD="align: center"]12240[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Mercer County Mustard Chinos[/TD]
[TD="align: center"]12240[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Mercer County Mustard Chinos[/TD]
[TD="align: center"]12240[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Mercer County Mustard Chinos[/TD]
[TD="align: center"]12240[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Mercer County Rust Chinos[/TD]
[TD="align: center"]12240[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Mercer County Rust Chinos[/TD]
[TD="align: center"]12240[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Mercer County Rust Chinos[/TD]
[TD="align: center"]12240[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Mercer County Rust Chinos[/TD]
[TD="align: center"]12240[/TD]

[TD="bgcolor: #CACACA, align: center"]15[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Mercer County Rust Chinos[/TD]
[TD="align: center"]12240[/TD]

[TD="bgcolor: #CACACA, align: center"]16[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Mercer County Green *Chinos[/TD]
[TD="align: center"]12240[/TD]

[TD="bgcolor: #CACACA, align: center"]17[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Mercer County Green *Chinos[/TD]
[TD="align: center"]12240[/TD]

[TD="bgcolor: #CACACA, align: center"]18[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Mercer County Green *Chinos[/TD]
[TD="align: center"]12240[/TD]

[TD="bgcolor: #CACACA, align: center"]19[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Mercer County Green *Chinos[/TD]
[TD="align: center"]12240[/TD]

[TD="bgcolor: #CACACA, align: center"]20[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Mercer County Green *Chinos[/TD]
[TD="align: center"]12240[/TD]

[TD="bgcolor: #CACACA, align: center"]21[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Mercer County Blue *Chinos[/TD]

[TD="bgcolor: #CACACA, align: center"]22[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Mercer County Blue *Chinos[/TD]

[TD="bgcolor: #CACACA, align: center"]23[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Mercer County Blue *Chinos[/TD]

[TD="bgcolor: #CACACA, align: center"]24[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Mercer County Blue *Chinos[/TD]

[TD="bgcolor: #CACACA, align: center"]25[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Mercer County Blue *Chinos[/TD]

[TD="bgcolor: #CACACA, align: center"]26[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Dark Detroit Mens Chino[/TD]
[TD="align: center"]12409[/TD]

[TD="bgcolor: #CACACA, align: center"]27[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Dark Detroit Mens Chino[/TD]
[TD="align: center"]12409[/TD]

[TD="bgcolor: #CACACA, align: center"]28[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Dark Detroit Mens Chino[/TD]
[TD="align: center"]12409[/TD]

[TD="bgcolor: #CACACA, align: center"]29[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Dark Detroit Mens Chino[/TD]
[TD="align: center"]12409[/TD]

[TD="bgcolor: #CACACA, align: center"]30[/TD]
[TD="align: center"]Mens[/TD]
[TD="align: center"]Bottom[/TD]
[TD="align: center"]Casual Trouser[/TD]
[TD="align: center"]Dark Detroit Mens Chino[/TD]
[TD="align: center"]12409[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4
 
Dear Sir,
Yes the department,category & subcategory also plays a role towrads the result delivery.
I have tweaked your formula and have got the final result.
Ths following is the snapshot.

I see you already added the additional conditions as required.
icon7.png


Once again thanks a lot for all your input & help.
...

You are welcome. Thanks for providing feedback.
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

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