counting unique instances in data set meeting multiple criteria

[rjheibel]

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.

	A	B	C	D	E	F	G
1						Start date	11/1/2018
2						End Date	2/17/2018
3	Date	Person	Code
4	15-Feb	Bob	100
5	15-Feb	Bob	110
6	15-Feb	Bob	300				Days with an entry
7	15-Feb	Frank	500			Bob	3
8	15-Feb	Frank	100			Frank	2
9	15-Feb	Frank	200			Bill	2
10	15-Feb	Bill	250			Mike	0
11	16-Feb	Bob	100
12	16-Feb	Bob	110
13	16-Feb	Bob	300
14	16-Feb	Bob	310
15	16-Feb	Bob	320
16	16-Feb	Bob	200
17	16-Feb	Bob	210
18	16-Feb	Frank	500
19	17-Feb	Bill	100
20	17-Feb	Bill	110
21	17-Feb	Bill	300
22	17-Feb	Bill	210
23	17-Feb	Bob	300
24	17-Feb	Bob	210
25	17-Feb	Bob	100

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[Jeffrey Mahoney]

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

 

[Eric W]

Here's one way:

Excel 2012

	A	B	C	D	E	F	G
1						Start date	11/1/2017
2						End Date	2/17/2018
3	Date	Person	Code
4	15-Feb	Bob	100
5	15-Feb	Bob	110
6	15-Feb	Bob	300				Days with an entry
7	15-Feb	Frank	500			Bob	3
8	15-Feb	Frank	100			Frank	2
9	15-Feb	Frank	200			Bill	2
10	15-Feb	Bill	250			Mike	0
11	16-Feb	Bob	100
12	16-Feb	Bob	110
13	16-Feb	Bob	300
14	16-Feb	Bob	310
15	16-Feb	Bob	320
16	16-Feb	Bob	200
17	16-Feb	Bob	210
18	16-Feb	Frank	500
19	17-Feb	Bill	100
20	17-Feb	Bill	110
21	17-Feb	Bill	300
22	17-Feb	Bill	210
23	17-Feb	Bob	300
24	17-Feb	Bob	210
25	17-Feb	Bob	100

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Array Formulas Cell Formula G7 {=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)))} <thead> </thead><tbody> </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>

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

 

[Jeffrey Mahoney]

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

 

[rjheibel]

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.

 

[Eric W]

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.

 

[Jeffrey Mahoney]

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.

 

[Eric W]

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.

 

[Jeffrey Mahoney]

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.