count unique values based on multiple criteria

[yuhjah]

Hello everyone. Really struggling with something - I know this has been posted in other places, but I'm having a hard time understanding the solutions.

I'm trying to create a function (not in vba) that will count the unique IDs where it fits the criteria: Date>8/2, Date<=8/7, Hours>0, Type="Driver"

I think I can use the Sum, Frequency, If functions, but I can't seem to understand the syntax.

Any help would be greatly appreciated! Pls help!

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]8/2/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8/10/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Driver[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]ID[/TD]
[TD]Type[/TD]
[TD]Hours[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1[/TD]
[TD]Driver[/TD]
[TD]1[/TD]
[TD]8/1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2[/TD]
[TD]Passenger[/TD]
[TD]5[/TD]
[TD]8/2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]2[/TD]
[TD]Passenger[/TD]
[TD]2[/TD]
[TD]8/3[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3[/TD]
[TD]Driver[/TD]
[TD]5[/TD]
[TD]8/5[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]4[/TD]
[TD]Driver[/TD]
[TD]5[/TD]
[TD]8/6[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]5[/TD]
[TD]Driver[/TD]
[TD]0[/TD]
[TD]8/7[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]5[/TD]
[TD]Driver[/TD]
[TD]2[/TD]
[TD]8/8[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]6[/TD]
[TD]Driver[/TD]
[TD]2[/TD]
[TD]8/9[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]6[/TD]
[TD]Driver[/TD]
[TD]0[/TD]
[TD]8/10[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Excellent problem description. Providing the expected count would be nice too.

<strike></strike>[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

2​

[/td][td]

8/2/2015​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

3​

[/td][td]

8/10/2015​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

4​

[/td][td]Driver[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

5​

[/td][td]

4​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

6​

[/td][td]ID[/td][td]Type[/td][td]Hours[/td][td]Date[/td][/tr]


[tr][td]

7​

[/td][td]

1​

[/td][td]Driver[/td][td]

1​

[/td][td]

8/1/2015​

[/td][/tr]


[tr][td]

8​

[/td][td]

2​

[/td][td]Passenger[/td][td]

5​

[/td][td]

8/2/2015​

[/td][/tr]


[tr][td]

9​

[/td][td]

2​

[/td][td]Passenger[/td][td]

2​

[/td][td]

8/3/2015​

[/td][/tr]


[tr][td]

10​

[/td][td]

3​

[/td][td]Driver[/td][td]

5​

[/td][td]

8/5/2015​

[/td][/tr]


[tr][td]

11​

[/td][td]

4​

[/td][td]Driver[/td][td]

5​

[/td][td]

8/6/2015​

[/td][/tr]


[tr][td]

12​

[/td][td]

5​

[/td][td]Driver[/td][td]

0​

[/td][td]

8/7/2015​

[/td][/tr]


[tr][td]

13​

[/td][td]

5​

[/td][td]Driver[/td][td]

2​

[/td][td]

8/8/2015​

[/td][/tr]


[tr][td]

14​

[/td][td]

6​

[/td][td]Driver[/td][td]

2​

[/td][td]

8/9/2015​

[/td][/tr]


[tr][td]

15​

[/td][td]

6​

[/td][td]Driver[/td][td]

0​

[/td][td]

8/10/2015​

[/td][/tr]
[/table]

In A5 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($B$7:$B$15=A4,IF($C$7:$C$15>0,
    IF($D$7:$D$15>=A2,IF($D$7:$D$15<=A3,$A$7:$A$15)))),
    $A$7:$A$15),1))

Note that the formula assumes the IDs to be numbers as displayed in your exhibit. If this assumption does not hold, the formula must be adjusted accordingly.

 

[SpreedsheetCrusader]

or you can do it through a simpler formula that is based on Boolean multiplication go through it carefully and you will see it is easy to follow:

Excel 2010
	C	D	E	F	G	H	I
2
3		count	4
4
5		8/2/2015
6		8/10/2015
7		Driver
8
9		ID	Type	Hours	Date
10		1	Driver	1	1-Aug
11		2	Passenger	5	2-Aug
12		2	Passenger	2	3-Aug
13		3	Driver	5	5-Aug
14		4	Driver	5	6-Aug
15		5	Driver	0	7-Aug
16		5	Driver	2	8-Aug
17		6	Driver	2	9-Aug
18		6	Driver	0	10-Aug
19
20
21
Sheet1

Cell Formulas
Range	Formula
E3	{=COUNT(IF(($E$10:$E$18=D7)*($F$10:$F$18>0)*($G$10:$G$18>D5)*($G$10:$G$18<=D6),(D10:D18)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

or you can do it through a simpler formula that is based on Boolean multiplication go through it carefully and you will see it is easy to follow:
[...]
{=COUNT(IF(($E$10:$E$18=D7)*($F$10:$F$18>0)*($G$10:$G$18>D5)*($G$10:$G$18<=D6),(D10:D18)))}
[...]

I fail to see why a bunch of pairwise multiplications instead of a bunch of IFs is a "simpler formula". By the way, I don't think that this formula fits the bill as "count unique values" is what is asked for.

 

[yuhjah]

or you can do it through a simpler formula that is based on Boolean multiplication go through it carefully and you will see it is easy to follow:

Excel 2010

	C	D	E	F	G	H	I
count
Driver
ID	Type	Hours	Date
Driver
Passenger
Passenger
Driver
Driver
Driver
Driver
Driver
Driver

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8/2/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8/10/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1-Aug[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]2-Aug[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]3-Aug[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]5-Aug[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]6-Aug[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]7-Aug[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]8-Aug[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]9-Aug[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]10-Aug[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]{=COUNT(IF(($E$10:$E$18=D7)*($F$10:$F$18>0)*($G$10:$G$18>D5)*($G$10:$G$18<=D6),(D10:D18)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Thank you so much for the suggestion. I can at least understand the syntax here. However, this is just returning the count for me, as opposed to the unique values.

 

[yuhjah]

Excellent problem description. Providing the expected count would be nice too.

<strike></strike>[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

8/2/2015​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

8/10/2015​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Driver[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]ID[/TD]
[TD]Type[/TD]
[TD]Hours[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

1​

[/TD]
[TD]Driver[/TD]
[TD]

1​

[/TD]
[TD]

8/1/2015​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

2​

[/TD]
[TD]Passenger[/TD]
[TD]

5​

[/TD]
[TD]

8/2/2015​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

2​

[/TD]
[TD]Passenger[/TD]
[TD]

2​

[/TD]
[TD]

8/3/2015​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

3​

[/TD]
[TD]Driver[/TD]
[TD]

5​

[/TD]
[TD]

8/5/2015​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

4​

[/TD]
[TD]Driver[/TD]
[TD]

5​

[/TD]
[TD]

8/6/2015​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

5​

[/TD]
[TD]Driver[/TD]
[TD]

0​

[/TD]
[TD]

8/7/2015​

[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

5​

[/TD]
[TD]Driver[/TD]
[TD]

2​

[/TD]
[TD]

8/8/2015​

[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

6​

[/TD]
[TD]Driver[/TD]
[TD]

2​

[/TD]
[TD]

8/9/2015​

[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]

6​

[/TD]
[TD]Driver[/TD]
[TD]

0​

[/TD]
[TD]

8/10/2015​

[/TD]
[/TR]
</tbody>[/TABLE]


In A5 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($B$7:$B$15=A4,IF($C$7:$C$15>0,
    IF($D$7:$D$15>=A2,IF($D$7:$D$15<=A3,$A$7:$A$15)))),
    $A$7:$A$15),1))

Note that the formula assumes the IDs to be numbers as displayed in your exhibit. If this assumption does not hold, the formula must be adjusted accordingly.

Thank you sooooo much! That worked amazingly! And while I don't necessarily understand it, I can at least change it to suit my needs!

Thank you again!

 

[SpreedsheetCrusader]

sorry i did not see the unique count criteria in your post, it should look like this now

Excel 2010
	C	D	E	F	G	H	I
1
2
3		count	4
4
5		8/2/2015
6		8/10/2015
7		Driver
8
9		ID	Type	Hours	Date
10		1	Driver	1	1-Aug
11		2	Passenger	5	2-Aug
12		2	Passenger	2	3-Aug
13		3	Driver	5	5-Aug
14		4	Driver	5	6-Aug
15		5	Driver	0	7-Aug
16		5	Driver	2	8-Aug
17		6	Driver	2	9-Aug
18		6	Driver	1	10-Aug
19
20
21
Sheet1

Cell Formulas
Range	Formula
E3	{=SUM(IF(FREQUENCY(IF(($E$10:$E$18=D7)*($F$10:$F$18>0)*($G$10:$G$18>D5)*($G$10:$G$18<=D6),(D10:D18)),D10:D18),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

i added the FREQUANCY trick Dr. Aladin Suggested, however i did not understand how it work in fetching the unique values only. i tried to evaluate the formula to understand it but i could not.

I wish if you can explain how does FREQUANCY fits in the equation that will be very helpful for the understanding.

thanks

 

[Aladin Akyurek]

Thank you sooooo much! That worked amazingly! And while I don't necessarily understand it, I can at least change it to suit my needs!

Thank you again!

You are welcome.

 

[Aladin Akyurek]

sorry i did not see the unique count criteria in your post, it should look like this now

Why do you insist on replacing IF with pairwise multiplications? The qualifier "Boolean" does not make them any special at all. So I for one do not favor such re-writes. See: http://www.mrexcel.com/forum/excel-questions/292473-performance-formulas-unique-count.html

i added the FREQUANCY trick Dr. Aladin Suggested, however i did not understand how it work in fetching the unique values only. i tried to evaluate the formula to understand it but i could not.

I wish if you can explain how does FREQUANCY fits in the equation that will be very helpful for the understanding.

thanks

See:
http://www.mrexcel.com/forum/excel-questions/818547-match-numbers.html#post3996274
http://www.mrexcel.com/forum/excel-questions/633564-counting-duplicates-using-sum-if-frequency-match-2.html#post3156949

Note. The MATCH bit delivers numbers in the latter link. Here in the case under consideration, we already have numbers (IDs are numeric.).

 

[SpreedsheetCrusader]

Why do you insist on replacing IF with pairwise multiplications?

saw your thread, now i understand the performance issues, it is just a personal preference and a habit i developed to use pairwise multiplication it helps me revisit the formula anytime at ease and understand the criteria involved, in addition when i nest multiple IF i tend to miss some brackets and i find it difficult to re-visit the formula, fix it or understand it after a while.

thanks for the post i know understand how to involve FREQUANCY to get the unique count of members of a set.

thanks