# count unique values based on multiple criteria

#### yuhjah

##### New Member
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!

 A B C D 1 2 8/2/2015 3 8/10/2015 4 Driver 5 6 ID Type Hours Date 7 1 Driver 1 8/1 8 2 Passenger 5 8/2 9 2 Passenger 2 8/3 10 3 Driver 5 8/5 11 4 Driver 5 8/6 12 5 Driver 0 8/7 13 5 Driver 2 8/8 14 6 Driver 2 8/9 15 6 Driver 0 8/10

<tbody>
</tbody>

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

<strike></strike>
 Row\Col A​ B​ C​ D​ 1​ 2​ 8/2/2015​ 3​ 8/10/2015​ 4​ Driver 5​ 4​ 6​ ID Type Hours Date 7​ 1​ Driver 1​ 8/1/2015​ 8​ 2​ Passenger 5​ 8/2/2015​ 9​ 2​ Passenger 2​ 8/3/2015​ 10​ 3​ Driver 5​ 8/5/2015​ 11​ 4​ Driver 5​ 8/6/2015​ 12​ 5​ Driver 0​ 8/7/2015​ 13​ 5​ Driver 2​ 8/8/2015​ 14​ 6​ Driver 2​ 8/9/2015​ 15​ 6​ Driver 0​ 8/10/2015​

In A5 control+shift+enter, not just enter:
Rich (BB code):
``````
=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.

##### Board Regular
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:

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

##### MrExcel MVP
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

##### New Member
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
CDEFGHI
2
3count4
4
58/2/2015
68/10/2015
7Driver
8
9IDTypeHoursDate
101Driver11-Aug
112Passenger52-Aug
122Passenger23-Aug
133Driver55-Aug
144Driver56-Aug
155Driver07-Aug
165Driver28-Aug
176Driver29-Aug
186Driver010-Aug
19
20
21

</tbody>
Sheet1

Array Formulas
CellFormula
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)))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

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

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

<strike></strike>
 Row\Col A​ B​ C​ D​ 1​ 2​ 8/2/2015​ 3​ 8/10/2015​ 4​ Driver 5​ 4​ 6​ ID Type Hours Date 7​ 1​ Driver 1​ 8/1/2015​ 8​ 2​ Passenger 5​ 8/2/2015​ 9​ 2​ Passenger 2​ 8/3/2015​ 10​ 3​ Driver 5​ 8/5/2015​ 11​ 4​ Driver 5​ 8/6/2015​ 12​ 5​ Driver 0​ 8/7/2015​ 13​ 5​ Driver 2​ 8/8/2015​ 14​ 6​ Driver 2​ 8/9/2015​ 15​ 6​ Driver 0​ 8/10/2015​

<tbody>
</tbody>

In A5 control+shift+enter, not just enter:
Rich (BB code):
``````
=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!

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

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

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

##### MrExcel MVP
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.

##### MrExcel MVP
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.).

##### Board Regular
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

1,081,680
Messages
5,360,506
Members
400,589
Latest member
Mikealphatangoc

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...