counting unique instances in data set meeting multiple criteria

R

rjheibel

New Member
Joined
Mar 8, 2018
Messages
43
Office Version
  1. 365
Platform
  1. Windows
I need a formula that will be able to count the number of days that a persons name appears in a large data set within a defined date range. The issue I am having is that the persons name will likely have multiple entries on the same day, and i only want to count the first instance. Any help is greatly appreciated.

An example data set is below. I want to determine formula in G7:G10 to count how many days the people listed in F7:F10 have an entry in the dataset A4:C25, within the date range in G1:G2.


ABCDEFG
Start date
End Date
Date PersonCode
Bob
Bob
BobDays with an entry
FrankBob
FrankFrank
FrankBill
BillMike
Bob
Bob
Bob
Bob
Bob
Bob
Bob
Frank
Bill
Bill
Bill
Bill
Bob
Bob
Bob

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

[TD="align: right"]11/1/2018[/TD]

[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"]2/17/2018[/TD]

[TD="align: center"]3[/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"]15-Feb[/TD]

[TD="align: right"]100[/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"]15-Feb[/TD]

[TD="align: right"]110[/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"]15-Feb[/TD]

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

[TD="align: center"]7[/TD]
[TD="align: right"]15-Feb[/TD]

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

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

[TD="align: center"]8[/TD]
[TD="align: right"]15-Feb[/TD]

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

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

[TD="align: center"]9[/TD]
[TD="align: right"]15-Feb[/TD]

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

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

[TD="align: center"]10[/TD]
[TD="align: right"]15-Feb[/TD]

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

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

[TD="align: center"]11[/TD]
[TD="align: right"]16-Feb[/TD]

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

[TD="align: center"]12[/TD]
[TD="align: right"]16-Feb[/TD]

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

[TD="align: center"]13[/TD]
[TD="align: right"]16-Feb[/TD]

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

[TD="align: center"]14[/TD]
[TD="align: right"]16-Feb[/TD]

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

[TD="align: center"]15[/TD]
[TD="align: right"]16-Feb[/TD]

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

[TD="align: center"]16[/TD]
[TD="align: right"]16-Feb[/TD]

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

[TD="align: center"]17[/TD]
[TD="align: right"]16-Feb[/TD]

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

[TD="align: center"]18[/TD]
[TD="align: right"]16-Feb[/TD]

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

[TD="align: center"]19[/TD]
[TD="align: right"]17-Feb[/TD]

[TD="align: right"]100[/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"]17-Feb[/TD]

[TD="align: right"]110[/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"]17-Feb[/TD]

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

[TD="align: center"]22[/TD]
[TD="align: right"]17-Feb[/TD]

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

[TD="align: center"]23[/TD]
[TD="align: right"]17-Feb[/TD]

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

[TD="align: center"]24[/TD]
[TD="align: right"]17-Feb[/TD]

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

[TD="align: center"]25[/TD]
[TD="align: right"]17-Feb[/TD]

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

</tbody>
Sheet1​
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
This is the way I would do it. Now, one of the MVP's probably has some really cool formula.....

Create a helper column that gives you the count of how many times a person has an entry per day in column D, Cell D4
=COUNTIFS(A$3:A3,A4,B$3:B3,B4)
Yes, I did want to include the headers in the formula. It counts how many previous instances of the combination name/date occurs.

In cell G7 put:
=COUNTIFS($A$4:$A$25,">"&$G$1-1,$A$4:$A$25,"<"&$G$2+1,$B$4:$B$25,F7,$D$4:$D$25,0)
and copy down

Jeff
 
Upvote 0
Here's one way:

Excel 2012
ABCDEFG
Start date
End Date
DatePersonCode
Bob
Bob
BobDays with an entry
FrankBob
FrankFrank
FrankBill
BillMike
Bob
Bob
Bob
Bob
Bob
Bob
Bob
Frank
Bill
Bill
Bill
Bill
Bob
Bob
Bob

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

[TD="align: right"]11/1/2017[/TD]

[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"]2/17/2018[/TD]

[TD="align: center"]3[/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"]15-Feb[/TD]

[TD="align: right"]100[/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"]15-Feb[/TD]

[TD="align: right"]110[/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"]15-Feb[/TD]

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

[TD="align: center"]7[/TD]
[TD="align: right"]15-Feb[/TD]

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

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

[TD="align: center"]8[/TD]
[TD="align: right"]15-Feb[/TD]

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

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

[TD="align: center"]9[/TD]
[TD="align: right"]15-Feb[/TD]

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

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

[TD="align: center"]10[/TD]
[TD="align: right"]15-Feb[/TD]

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

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

[TD="align: center"]11[/TD]
[TD="align: right"]16-Feb[/TD]

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

[TD="align: center"]12[/TD]
[TD="align: right"]16-Feb[/TD]

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

[TD="align: center"]13[/TD]
[TD="align: right"]16-Feb[/TD]

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

[TD="align: center"]14[/TD]
[TD="align: right"]16-Feb[/TD]

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

[TD="align: center"]15[/TD]
[TD="align: right"]16-Feb[/TD]

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

[TD="align: center"]16[/TD]
[TD="align: right"]16-Feb[/TD]

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

[TD="align: center"]17[/TD]
[TD="align: right"]16-Feb[/TD]

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

[TD="align: center"]18[/TD]
[TD="align: right"]16-Feb[/TD]

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

[TD="align: center"]19[/TD]
[TD="align: right"]17-Feb[/TD]

[TD="align: right"]100[/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"]17-Feb[/TD]

[TD="align: right"]110[/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"]17-Feb[/TD]

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

[TD="align: center"]22[/TD]
[TD="align: right"]17-Feb[/TD]

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

[TD="align: center"]23[/TD]
[TD="align: right"]17-Feb[/TD]

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

[TD="align: center"]24[/TD]
[TD="align: right"]17-Feb[/TD]

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

[TD="align: center"]25[/TD]
[TD="align: right"]17-Feb[/TD]

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

</tbody>
Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G7[/TH]
[TD="align: left"]{=SUM(SIGN(FREQUENCY(IF($B$4:$B$25=F7,IF($A$4:$A$25>=$G$1,IF($A$4:$A$25<=$G$2,$A$4:$A$25))),$A$4:$A$25)))}[/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]



I changed all the dates in column A to be 2018, and I changed the G1 date too.

(And I do think it's kind of a cool formula! But the option in post 2 also works, and is probably simpler to understand and maintain.)
 
Upvote 0
Yup, Cool Formula!

So Eric, do you maintain a list of formulas in general categories just for this purpose? Or have you created similar formulas so often that you can pop them into a new format?

I used to think dynamic named ranges were magic until I understood how they worked. Now people look at me think I'm magic because I can remember that. I'm going to pick the formula apart as I have tried on similar "Array" formulas and try to understand it logically. It just doesn't appear, at first, to fall into a standard Excel Function format, so it seems foreign to me.

Jeff
 
Upvote 0
Eric C, THANKS! Works perfect. Exactly what I was looking for.

Jeff, thanks for your response as well, but I was looking for a solution without adding additional columns / steps.
 
Last edited:
Upvote 0
rjheibel, glad we could help! :)

Jeff, it's pretty much like you thought. When I started here, array formulas were a foreign language, and I didn't even know FREQUENCY existed, let alone how to use it in this context. But I picked apart anything that looked interesting until I figured it out, and after a while I could recognize when a certain formula would work in a given situation, and how to adapt it. I have a large library of links for useful information, but I don't refer to it as much anymore. I still look for interesting topics though.
 
Upvote 0
Eric,

Please let me know if my evaluation is correct.

The red part of the Formula (f) is normally the array you want to count. In this case, you have added nested if statements to check the person and date range.

The Blue part of the f is the date range as the part returned. Could this have been column B as well? Normally Excel would have given values down the column as a return, but in this case it returns an array of values instead of bins of data.

SIGN then returns any value above zero as 1 and any zero as zero.

Sum then counts all the 1's.

=SUM(SIGN(FREQUENCY(IF($B$4:$B$25=F7,IF($A$4:$A$25>=$G$1,IF($A$4:$A$25<=$G$2,$A$4:$A$25))),$A$4:$A$25)))

What is strange for me is that Frequency doesn't normally evaluate IF a value meets the criteria, it just returns Bins of data.
 
Upvote 0
Jeff,

No, you couldn't use column B instead of A. We want to find out how many unique days for each person, not how many unique people. As you noted, FREQUENCY puts numbers into bins. First, B is text, not numbers. We can get around that by using MATCH to convert them to numbers, but that still won't give us what we want. We want to make each bin represent one day, so that each bin will contain the number of times that day is used. Then the SIGN changes those values to 0s and 1s. It's convenient that dates are saved as numbers.
 
Upvote 0
Thank you. It's logical. I understand if I think about it. Now I need to use it on a regular basis so it becomes a wrote and modular tool.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Latest Data pull-up from the corresponding date
Replies
8
Views
198
RobP
R
S
  • Question Question
Latest Data Pull-up from the corresponding date-Single party
Replies
3
Views
125
Sergius
S
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
356
dilshod_k
D
Vintage79
  • Solved
Count consecutive days and gaps between days
Replies
2
Views
216
Vintage79
Vintage79
CubaRJ
  • Question Question
Calculate Overtime with 2 codes for the same date
Replies
0
Views
187
CubaRJ
CubaRJ

Forum statistics

Threads
1,223,790
Messages
6,174,594
Members
452,574
Latest member
hang_and_bang

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