Conditional Count Related Formula Help

X

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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Hi,

SUMPRODUCT may be the answer. I've used validation list to select the items that I want to test against...including the GRN

Excel Workbook
ABCDE
1DeptCatSub CatStyleID
2MensBottomCasual TrouserChinos12240
3
43
5
6MensBottomCasual TrouserChinos12240
7MensBottomCasual TrouserChinos12240
8WomensTopCasual TrouserJeans12240
9MensBottomDress TrouserSuit12240
10MensBottomCasual TrouserChinos12240
11WomensBottomCasual TrouserChinos
12MensTopCasual TrouserJeans
13MensBottomDress TrouserSuit
14WomensBottomCasual TrouserChinos12333
15MensBottomCasual TrouserChinos12333
16MensBottomCasual TrouserJeans12333
Sheet1
#VALUE!



AP
 
Upvote 0
Hi,
Thank you for your input and effort.
I am looking for the result in Cell E2 which is not dependent on a particular GRN number.
For example in this case, there are 2 GRN numbers 12240 & 12409 in column E.
Against these 2 GRN numbers thare are 4 unique count of styles( Mercer County Mustard Chinos/Mercer County Rust Chinosi/Mercer County Green Chinos & Dark Detroit Mens Chino) in column D.
I would like to have a formula in Cell E2 which can check the GRN numbers across the column E and then return the Unique Count Of Styles across Column D against the specific Department, Category,Subcategory across B2:D2.
Regards
 
Upvote 0
Dear Sir,
There are 2 unique GRN numbers in Column E under which there are 4 Unique Styles in column D.
I intend to calculate how many unique styles are there under all GRNs(in column E).
I plan to make a summary at the top of the data set in Cell E2.
This formula in this cell need to check whether the GRN columns are filled in or not and if filled in then populate the unique count of styles against the specific Department, Category & Sub Category spread across B2:D2.
Regards
 
Upvote 0
xlmaniac said:
Hi,
Thank you for your input and effort.
I am looking for the result in Cell E2 which is not dependent on a particular GRN number.
For example in this case, there are 2 GRN numbers 12240 & 12409 in column E.
Against these 2 GRN numbers thare are 4 unique count of styles( Mercer County Mustard Chinos/Mercer County Rust Chinosi/Mercer County Green Chinos & Dark Detroit Mens Chino) in column D.
I would like to have a formula in Cell E2 which can check the GRN numbers across the column E and then return the Unique Count Of Styles across Column D against the specific Department, Category,Subcategory across B2:D2.
Regards
Click to expand...

E2, control+shift+enter, not just enter:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF(D6:D30<>"",IF(ISNUMBER($E$6:$E$30),
  MATCH("~"&$D$6:$D$30,$D$6:$D$30&"",0))),
   ROW($D$6:$D$30)-ROW($D$6)+1),1))
 
Upvote 0
Dear Sir,
Thank you so much for helping me yet again with your mesmerising solutions.
I really feel honoured to learn excel from the excel gurus like you.
Pls accept my sincere gratitude for the same.
Regards
 
Upvote 0
Ahh I see...I made a slight change...how's this?

Excel Workbook
ABCDE
44
5
6MensBottomCasual TrouserChinos12240
7MensBottomCasual TrouserChinos12240
8WomensTopCasual TrouserJeans12240
9MensBottomDress TrouserSuit12240
10MensBottomCasual TrouserChinos12409
11MensBottomCasual TrouserChinos
12MensTopCasual TrouserJeans
13MensBottomDress TrouserSuit
14WomensBottomCasual TrouserChinos12409
15MensBottomCasual TrouserChinos12409
16MensBottomCasual TrouserJeans12409
Sheet1



AP
 
Upvote 0
Hi AP,
This is also cool..;)
Thank you so much for your effort and help.
Really appreciate the same.
Regards
 
Upvote 0
xlmaniac said:
Dear Sir,
Thank you so much for helping me yet again with your mesmerising solutions.
I really feel honoured to learn excel from the excel gurus like you.
Pls accept my sincere gratitude for the same.
Regards
Click to expand...

You are welcome. The formula effects a distinct count, where department, category, and sub category do not play any role. Should they?
 
Upvote 0
Aladin Akyurek said:
You are welcome. The formula effects a distinct count, where department, category, and sub category do not play any role. Should they?
Click to expand...

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.
Once again thanks a lot for all your input & help.
Excel Workbook
HIJKL
1DepartmentCategorySub Category# Style(Desired Result)
2MensBottomCasual Trouser4
3
4
5DepartmentCategorySub CategoryStyle NameGRN No
6MensBottomCasual TrouserMercer County Mustard Chinos12240
7MensBottomCasual TrouserMercer County Mustard Chinos12240
8MensBottomCasual TrouserMercer County Mustard Chinos12240
9MensBottomCasual TrouserMercer County Mustard Chinos12240
10MensBottomCasual TrouserMercer County Mustard Chinos12240
11MensBottomCasual TrouserMercer County Rust Chinos12240
12MensBottomCasual TrouserMercer County Rust Chinos12240
13MensBottomCasual TrouserMercer County Rust Chinos12240
14MensBottomCasual TrouserMercer County Rust Chinos12240
15MensBottomCasual TrouserMercer County Rust Chinos12240
16MensBottomCasual TrouserMercer County Green Chinos12240
17MensBottomCasual TrouserMercer County Green Chinos12240
18MensBottomCasual TrouserMercer County Green Chinos12240
19MensBottomCasual TrouserMercer County Green Chinos12240
20MensBottomCasual TrouserMercer County Green Chinos12240
21MensBottomCasual TrouserMercer County Blue Chinos
22MensBottomCasual TrouserMercer County Blue Chinos
23MensBottomCasual TrouserMercer County Blue Chinos
24MensBottomCasual TrouserMercer County Blue Chinos
25MensBottomCasual TrouserMercer County Blue Chinos
26MensBottomCasual TrouserDark Detroit Mens Chino12409
27MensBottomCasual TrouserDark Detroit Mens Chino12409
28MensBottomCasual TrouserDark Detroit Mens Chino12409
29MensBottomCasual TrouserDark Detroit Mens Chino12409
30MensBottomCasual TrouserDark Detroit Mens Chino12409
12
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Need formula or VBA count the delays
2
Replies
13
Views
2K
motilulla
M
C
  • Question Question
Formula needed to count repeated data based on criterias
Replies
1
Views
850
J.Ty.
J.Ty.
M
  • Question Question
Look up in row constant occurrences
Replies
7
Views
754
MickG
M
R
  • Question Question
Help using formulas - conditional sumproduct???
Replies
4
Views
705
Eric W
Eric W
R
  • Question Question
Multiple Criteria Array Formula Help
Replies
2
Views
494
RicardoCubed
R

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

Share this page

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