Count 30, 60 and over 90 days based on Name in Column "A"

L

leveyc

Board Regular
Joined
Oct 14, 2009
Messages
163
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi,

I my first report has 2000 rows with approx. 300 unique names in column "A", each row has a date associated and I want to take the unique list and then add four columns, column "C" 0-30, column "D" 31-61, column "E" 61-90 and "F" over 90 days, in each column I want the formula to lookup the unique name and look for the dates based on the column heading, each column could have a count of more than one.

All help appreciated,
Thanks
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Try:

Book2
ABCDEFGH
1NameDateNames000-030031-061062-090091-9999
2Bart1/1/2021Bart1004
3Maggie1/11/2021Maggie1226
4Homer1/21/2021Homer1113
5Lisa1/31/2021Lisa0004
6Maggie2/10/2021Marge0002
7Maggie2/20/2021
8Bart3/2/2021
9Bart3/12/2021
10Maggie3/22/2021
11Lisa4/1/2021
12Bart4/11/2021
13Marge4/21/2021
14Marge5/1/2021
15Lisa5/11/2021
16Maggie5/21/2021
17Homer5/31/2021
18Homer6/10/2021
19Maggie6/20/2021
20Lisa6/30/2021
21Maggie7/10/2021
22Homer7/20/2021
23Maggie7/30/2021
24Homer8/9/2021
25Maggie8/19/2021
26Maggie8/29/2021
27Homer9/8/2021
28Maggie9/18/2021
29Bart9/28/2021
30Lisa10/8/2021
31Maggie10/18/2021
Sheet4
Cell Formulas
RangeFormula
D2:D6D2=UNIQUE(A2:A31)
E2:H6E2=COUNTIFS($A$2:$A$31,$D2,$B$2:$B$31,"<="&TODAY()-LEFT(E$1,3),$B$2:$B$31,">="&TODAY()-MID(E$1,5,4))
Dynamic array formulas.
 
Upvote 0
Eric W said:
Try:

Book2
ABCDEFGH
1NameDateNames000-030031-061062-090091-9999
2Bart1/1/2021Bart1004
3Maggie1/11/2021Maggie1226
4Homer1/21/2021Homer1113
5Lisa1/31/2021Lisa0004
6Maggie2/10/2021Marge0002
7Maggie2/20/2021
8Bart3/2/2021
9Bart3/12/2021
10Maggie3/22/2021
11Lisa4/1/2021
12Bart4/11/2021
13Marge4/21/2021
14Marge5/1/2021
15Lisa5/11/2021
16Maggie5/21/2021
17Homer5/31/2021
18Homer6/10/2021
19Maggie6/20/2021
20Lisa6/30/2021
21Maggie7/10/2021
22Homer7/20/2021
23Maggie7/30/2021
24Homer8/9/2021
25Maggie8/19/2021
26Maggie8/29/2021
27Homer9/8/2021
28Maggie9/18/2021
29Bart9/28/2021
30Lisa10/8/2021
31Maggie10/18/2021
Sheet4
Cell Formulas
RangeFormula
D2:D6D2=UNIQUE(A2:A31)
E2:H6E2=COUNTIFS($A$2:$A$31,$D2,$B$2:$B$31,"<="&TODAY()-LEFT(E$1,3),$B$2:$B$31,">="&TODAY()-MID(E$1,5,4))
Dynamic array formulas.
Click to expand...
Hi, thanks for the quick response.....the only thing that will not work is counting from today, I need to count from the install date, install date is a field in the data, then I need to could everything from this point on......should have included in the original response

Thanks
 
Upvote 0
It's easy enough to use a date on your sheet instead of TODAY():

Book2
ABCDEFGH
1NameDateNames000-030031-061062-090091-9999
2Bart1/1/2021Bart1201
3Maggie1/11/2021Maggie0121
4Homer1/21/2021Homer0001
5Lisa1/31/2021Lisa1010
6Maggie2/10/2021Marge2000
7Maggie2/20/2021
8Bart3/2/2021
9Bart3/12/2021Install date:5/1/2021
10Maggie3/22/2021
11Lisa4/1/2021
12Bart4/11/2021
13Marge4/21/2021
14Marge5/1/2021
15Lisa5/11/2021
16Maggie5/21/2021
17Homer5/31/2021
18Homer6/10/2021
19Maggie6/20/2021
20Lisa6/30/2021
21Maggie7/10/2021
22Homer7/20/2021
23Maggie7/30/2021
24Homer8/9/2021
25Maggie8/19/2021
26Maggie8/29/2021
27Homer9/8/2021
28Maggie9/18/2021
29Bart9/28/2021
30Lisa10/8/2021
31Maggie10/18/2021
Sheet4
Cell Formulas
RangeFormula
D2:D6D2=UNIQUE(A2:A31)
E2:H6E2=COUNTIFS($A$2:$A$31,$D2,$B$2:$B$31,"<="&$E$9-LEFT(E$1,3),$B$2:$B$31,">="&$E$9-MID(E$1,5,4))
Dynamic array formulas.


Also, it would be helpful if you could update your user profile to show what version of Excel you're using. That lets us know what functions are available to work with.
 
Upvote 0
Eric W said:
It's easy enough to use a date on your sheet instead of TODAY():

Book2
ABCDEFGH
1NameDateNames000-030031-061062-090091-9999
2Bart1/1/2021Bart1201
3Maggie1/11/2021Maggie0121
4Homer1/21/2021Homer0001
5Lisa1/31/2021Lisa1010
6Maggie2/10/2021Marge2000
7Maggie2/20/2021
8Bart3/2/2021
9Bart3/12/2021Install date:5/1/2021
10Maggie3/22/2021
11Lisa4/1/2021
12Bart4/11/2021
13Marge4/21/2021
14Marge5/1/2021
15Lisa5/11/2021
16Maggie5/21/2021
17Homer5/31/2021
18Homer6/10/2021
19Maggie6/20/2021
20Lisa6/30/2021
21Maggie7/10/2021
22Homer7/20/2021
23Maggie7/30/2021
24Homer8/9/2021
25Maggie8/19/2021
26Maggie8/29/2021
27Homer9/8/2021
28Maggie9/18/2021
29Bart9/28/2021
30Lisa10/8/2021
31Maggie10/18/2021
Sheet4
Cell Formulas
RangeFormula
D2:D6D2=UNIQUE(A2:A31)
E2:H6E2=COUNTIFS($A$2:$A$31,$D2,$B$2:$B$31,"<="&$E$9-LEFT(E$1,3),$B$2:$B$31,">="&$E$9-MID(E$1,5,4))
Dynamic array formulas.


Also, it would be helpful if you could update your user profile to show what version of Excel you're using. That lets us know what functions are available to work with.
Click to expand...
Ah, that is better, but only counts days prior to the install date (found out because I got all zeros and played with the dates), all dates being counted will be after the install date.
 
Upvote 0
Just change the signs a bit:

Excel Formula: 
=COUNTIFS($A$2:$A$31,$D2,$B$2:$B$31,">="&$E$9+LEFT(E$1,3),$B$2:$B$31,"<="&$E$9+MID(E$1,5,4))
 
Upvote 0
Eric W said:
Just change the signs a bit:
Excel Formula: 
=COUNTIFS($A$2:$A$31,$D2,$B$2:$B$31,">="&$E$9+LEFT(E$1,3),$B$2:$B$31,"<="&$E$9+MID(E$1,5,4))
Click to expand...
Eric W said:
Just change the signs a bit:

Excel Formula: 
=COUNTIFS($A$2:$A$31,$D2,$B$2:$B$31,">="&$E$9+LEFT(E$1,3),$B$2:$B$31,"<="&$E$9+MID(E$1,5,4))
Click to expand...
Hi, Something is still not quite right, the formula counts two tickets for this install which is correct, but your formula only reports one, if I change the date on the ticket in July to June or October it will show, this seems to be across the board, very strange......any ideas?

Thanks

Install Date# of Tickets000-030031-060061-090091-9999
6/4/202121000
Two Tickets8/4/2021
6/8/2021
 
Upvote 0
It's only reporting 1 ticket because the second item has nothing in column A. That actually makes things a lot tougher. Any chance you could show some representative data for me to look at? Using the XL2BB tool would be ideal (see the link in my signature or the reply box). It's easy to download, install, and use. It's what I used to show examples. If that isn't an option, then at least a larger sample like you did in the last post, with more rows, and expected results. And what version of Excel are you using?
 
Upvote 0
Hi Eric, Your formula worked, when I doug in to the data I was given I found some anomalies where there were tickets created prior to the install date, which makes no sence as the install date it what kicks off the data being captured, long story short they fixed their issues and now the formula works as it should.....really appreciate the help.....Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Conditional Formatting if adjacent cell is >60 Days Ago and Targeted Cell is Blank
Replies
1
Views
727
6StringJazzer
6StringJazzer
BrettOlbrys1
  • Solved
Count Unique Values Based on Multiple Criteria
Replies
6
Views
466
BrettOlbrys1
BrettOlbrys1
ChrisMac1
  • Question Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
390
ChrisMac1
ChrisMac1
S
  • Solved
Conditional format dates in a range which are x days from today
Replies
3
Views
956
Sallyprit
S
C
  • Question Question
IF statement for 30, 60, 90 day overdue
Replies
4
Views
2K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,950
Messages
6,175,582
Members
452,653
Latest member
craigje92

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