Unique Count with Multiple criteria

[kiwiorn]

I am trying to count the number of cells that meet certain numerous criteria, using excel 2003.

For example, each job has a specific ref number and within that job ref number there is a number of lines of data, some of which we ignore and some of which we include. Each job will have a planning time using an old methodology (Planning A), a planning time using a new methodology (Planning B) and an actual time. There are a few other variables too such as the category of work.

I am trying to count the number of each unique ref no. within the sub category of work I am looking at (there are circa 50 sub categories of work). Each ref no. will only have 1 combination of work cat and sub category.

For example:

Ref# Cat Sub Rel PlanA PlanA Act
X001 A10 1000 Y 1.50 2.50 2.25
X001 A10 1000 Y 1.50 2.50 2.25
X001 A10 1000 N 0.00 0.00 0.00
X002 A10 2000 Y 2.00 1.50 1.70
X002 A10 2000 Y 2.00 1.50 1.70
X003 B10 1050 Y 3.00 3.50 6.90
X003 B10 1050 Y 3.00 3.50 6.90
X004 A10 1000 Y 2.00 1.50 4.00
X004 A10 1000 N 0.00 0.00 0.00
X005 C10 3000 N 0.00 0.00 0.00

I need to do a unique count for each individual work sub category where there is some relevant work done for each job (ref no.).

Cat Sub Count (if relevant)
A10 1000 2
A10 2000 1
B10 1050 1
C10 3000 0

Above there are only 4 relevant jobs in total. What is the count formula I use to achieve the unique count by subcategory above?

Regards
Richard

 

[Sandeep Warrier]

How about something like this??

Excel Workbook
	A	B	C	D	E	F	G
1	Ref#	Cat	Sub	Rel	PlanA	PlanA	Act
2	X001	A10	1000	Y	1.5	2.5	2.25
3	X001	A10	1000	Y	1.5	2.5	2.25
4	X001	A10	1000	N	0	0	0
5	X002	A10	2000	Y	2	1.5	1.7
6	X002	A10	2000	Y	2	1.5	1.7
7	X003	B10	1050	Y	3	3.5	6.9
8	X003	B10	1050	Y	3	3.5	6.9
9	X004	A10	1000	Y	2	1.5	4
10	X004	A10	1000	N	0	0	0
11	X005	C10	3000	N	0	0	0
15	X001	A10	2
Sheet1

Cell Formulas
Range	Formula
C15	=SUMPRODUCT(--($A$2:$A$11=A15),--($B$2:$B$11=B15),--($D$2:$D$11="Y"))

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[kiwiorn]

Thanks Sandeep. Problem is that I'm not concerned about a count for X001, only the total number of unique references that occur for all cat / sub cat A/1000 for instance. In this case it is 2, being:
X001 and X004

ie. Under the category combination of A 1000 there are 2 unique references that have one or more relevant (Y) lines.

A10/1000 = 2 (X001 and X004)
A10/2000 = 1 (X002)
B10/1050 = 1 (X003)
C10/3000 = 0 (X005 has no line that is relevant)

I think it requires a combination of several formulas using an array but I'm just not sure exactly how to get the unique aspect of the references into the formula.

 

[Sandeep Warrier]

Hello,

This should work.... used this for help.

Excel Workbook
	A	B	C	D	E	F	G
1	Ref#	Cat	Sub	Rel	PlanA	PlanA	Act
2	X001	A10	1000	Y	1.5	2.5	2.25
3	X001	A10	1000	Y	1.5	2.5	2.25
4	X001	A10	1000	N	0	0	0
5	X002	A10	2000	Y	2	1.5	1.7
6	X002	A10	2000	Y	2	1.5	1.7
7	X003	B10	1050	Y	3	3.5	6.9
8	X003	B10	1050	Y	3	3.5	6.9
9	X004	A10	1000	Y	2	1.5	4
10	X004	A10	1000	N	0	0	0
11	X005	C10	3000	N	0	0	0
12
13	A10	1000	2
Sheet1

#VALUE!

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Sandeep Warrier]

For all the 4 conditions that you mentioned...

Excel Workbook
	A	B	C	D	E	F	G
1	Ref#	Cat	Sub	Rel	PlanA	PlanA	Act
2	X001	A10	1000	Y	1.5	2.5	2.25
3	X001	A10	1000	Y	1.5	2.5	2.25
4	X001	A10	1000	N	0	0	0
5	X002	A10	2000	Y	2	1.5	1.7
6	X002	A10	2000	Y	2	1.5	1.7
7	X003	B10	1050	Y	3	3.5	6.9
8	X003	B10	1050	Y	3	3.5	6.9
9	X004	A10	1000	Y	2	1.5	4
10	X004	A10	1000	N	0	0	0
11	X005	C10	3000	N	0	0	0
12
13	A10	1000	2
14	A10	2000	1
15	B10	1050	1
16	C10	3000	0
Sheet1

#VALUE!

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[kiwiorn]

Thanks again Sandeep. This one does the trick nicely. I did have a couple of extra criteria to add in, and am curious, however, how this could be done if I had exceeded the number of nested "if" statements that are allowed (one more than I needed). Any ideas?!!!

 

[Aladin Akyurek]

Thanks again Sandeep. This one does the trick nicely. I did have a couple of extra criteria to add in, and am curious, however, how this could be done if I had exceeded the number of nested "if" statements that are allowed (one more than I needed). Any ideas?!!!

You can coalesce some of the IFs into a vector multiplication...

=SUM(IF(FREQUENCY(
    IF(($B$2:$B$11=A13)*($C$2:$C$11=B13)*($D$2:$D$11="Y"),
    IF($A$2:$A$11<>"",MATCH($A$2:$A$11,$A$2:$A$11,0))))),
      ROW($A$2:$A$11)-ROW($A$2)+1),1))

=SUM(IF(FREQUENCY(
    IF(($B$2:$B$11=A13)*($C$2:$C$11=B13)*($D$2:$D$11="Y"),
    IF($A$2:$A$11<>"",MATCH("~"&$A$2:$A$11,$A$2:$A$11&"",0))))),
      ROW($A$2:$A$11)-ROW($A$2)+1),1))

 

[Sandeep Warrier]

You can coalesce some of the IFs into a vector multiplication...


=SUM(IF(FREQUENCY(
IF(($B$2:$B$11=A13)*($C$2:$C$11=B13)*($D$2:$D$11="Y"),
IF($A$2:$A$11<>"",MATCH("~"&$A$2:$A$11,$A$2:$A$11&"",0))))),
ROW($A$2:$A$11)-ROW($A$2)+1),1))
[/code]

Hi Aladin,

In the equation MATCH("~"&$A$2:$A$11,$A$2:$A$11&"",0), what advantage does using "~" have over the regular function?

On going through the formula using Evaluate formula, I didnt find any difference in the way the formula progresses.

 

[Aladin Akyurek]

Hi Aladin,

In the equation MATCH("~"&$A$2:$A$11,$A$2:$A$11&"",0), what advantage does using "~" have over the regular function?

On going through the formula using Evaluate formula, I didnt find any difference in the way the formula progresses.

You need to find the threads in which the formula is developed. Anyway, they help escape the special meaning of chars like < that might appear in the items of interest.

 

[Aladin Akyurek]

You can coalesce some of the IFs into a vector multiplication...

=SUM(IF(FREQUENCY(
    IF(($B$2:$B$11=A13)*($C$2:$C$11=B13)*($D$2:$D$11="Y"),
    IF($A$2:$A$11<>"",MATCH($A$2:$A$11,$A$2:$A$11,0))))),
      ROW($A$2:$A$11)-ROW($A$2)+1),1))

=SUM(IF(FREQUENCY(
    IF(($B$2:$B$11=A13)*($C$2:$C$11=B13)*($D$2:$D$11="Y"),
    IF($A$2:$A$11<>"",MATCH("~"&$A$2:$A$11,$A$2:$A$11&"",0))))),
      ROW($A$2:$A$11)-ROW($A$2)+1),1))

Corrected for parens...

=SUM(IF(FREQUENCY(
    IF(($B$2:$B$11=A13)*($C$2:$C$11=B13)*($D$2:$D$11="Y"),
    IF($A$2:$A$11<>"",MATCH("~"&$A$2:$A$11,$A$2:$A$11&"",0))),
      ROW($A$2:$A$11)-ROW($A$2)+1),1))