Conditional Count Related Formula Help

xlmaniac

Well-known Member
Joined
Jul 2, 2009
Messages
527
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
1*DepartmentCategorySub Category# Style(Desired Result)
2*MensBottomCasual 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

<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>
</tbody>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
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

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

You are welcome. The formula effects a distinct count, where department, category, and sub category do not play any role. Should they?
 
Upvote 0
You are welcome. The formula effects a distinct count, where department, category, and sub category do not play any role. Should they?

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

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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