SUMPRODUCT(?) for 2 Criteria: 1st letter of last name (A:A) & days since date (B:B)

H

hannaboesch

New Member
Joined
Mar 9, 2016
Messages
5
Hello!
I need a formula which will return the number of rows meeting 2 criteria.
My data set: column A has patient names (Last, First); column B has Date of Appointment (mm/dd/yyyy)

How can I return the number of patients (to a dashboard) with last names A-G with appt date <30 days ago?
I also need for 30-45 days ago and 45+ days ago.

I used conditional formatting to highlight overdue dates, but not helpful for counting.

Here's my attempt so far, any suggestions? Would COUNTIF be better? Thank you!

=SUMPRODUCT((Export!A:A,{"a","b","c","D","E","F","G"}&"*"))*((Export!B:B=today()))
 
Sort by date Sort by votes
Why not a formula with COUNTIFS? Something like:

=SUM(COUNTIFS(Export!A:A,{"a","b","c","d","e","f","g"}&"*",Export!B:B,"<="&TODAY()-30))
 
Upvote 0
Thanks for your response. The formula isn't working, I'm not sure where its getting the values from either.

Heres my data set. The formula was returning 10 for <=30 and 7 for >45. =/

[TABLE="width: 132"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Apatient, A
[/TD]
[TD="align: right"]1/19/2016[/TD]
[/TR]
[TR]
[TD]Apatient, A[/TD]
[TD="align: right"]1/21/2016[/TD]
[/TR]
[TR]
[TD]Apatient, A[/TD]
[TD="align: right"]2/2/2016[/TD]
[/TR]
[TR]
[TD]Apatient, A[/TD]
[TD="align: right"]2/4/2016[/TD]
[/TR]
[TR]
[TD]Apatient, A[/TD]
[TD="align: right"]1/27/2016[/TD]
[/TR]
[TR]
[TD]Apatient, A[/TD]
[TD="align: right"]2/17/2016[/TD]
[/TR]
[TR]
[TD]Apatient, A[/TD]
[TD="align: right"]1/19/2016[/TD]
[/TR]
[TR]
[TD]Apatient, A[/TD]
[TD="align: right"]2/11/2016[/TD]
[/TR]
[TR]
[TD]Apatient, A[/TD]
[TD="align: right"]1/7/2016[/TD]
[/TR]
[TR]
[TD]Apatient, A[/TD]
[TD="align: right"]2/2/2016[/TD]
[/TR]
[TR]
[TD]Apatient, A[/TD]
[TD="align: right"]1/21/2016[/TD]
[/TR]
[TR]
[TD]Apatient, A[/TD]
[TD="align: right"]2/3/2016[/TD]
[/TR]
[TR]
[TD]Apatient, A[/TD]
[TD="align: right"]2/11/2016[/TD]
[/TR]
[TR]
[TD]Apatient, A[/TD]
[TD="align: right"]1/14/2016[/TD]
[/TR]
[TR]
[TD]Apatient, A[/TD]
[TD="align: right"]1/27/2016[/TD]
[/TR]
[TR]
[TD]Apatient, A[/TD]
[TD="align: right"]2/4/2016[/TD]
[/TR]
[TR]
[TD]Apatient, A[/TD]
[TD="align: right"]1/27/2016
[/TD]
[/TR]
[TR]
[TD]Apatient, A[/TD]
[TD="align: right"]1/14/2016
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Instead of reporting what the formula returns, you should say what the result must be for the data set you posted.
 
Upvote 0
Sorry, I suppose that would be helpful! The <30 formula should return 3.

The >45 formula returns the correct value for this data set. But.. I am sorting based on three sets for last names: A-G, H-N, and O-Z. The >45 formula does not work for the H-N and O-Z sets.
 
Upvote 0
hannaboesch said:
Sorry, I suppose that would be helpful! The <30 formula should return 3.

The >45 formula returns the correct value for this data set. But.. I am sorting based on three sets for last names: A-G, H-N, and O-Z. The >45 formula does not work for the H-N and O-Z sets.
Click to expand...

Could you repost that? Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl is also a method for posting an exhibit.
 
Upvote 0

Excel 2012
ABCDEFG
1Patient NameBaseline Date
2Apatient, A1/19/2016# Pts with Baseline# Pts 0-30 days# Pts 30-45 days# Pts >45days
3Apatient, A1/21/2016A-G12107
4Apatient, A2/2/2016H-N2414
5Apatient, A2/4/2016O-Z158
6Apatient, A1/27/2016
7Apatient, A2/17/2016
8Apatient, A1/19/2016
9Apatient, A2/11/2016
10Apatient, A1/7/2016
11Apatient, A2/2/2016
12Apatient, A1/21/2016
13Apatient, A2/3/2016
14Hpatient, H2/11/2016
15Hpatient, H1/14/2016
16Hpatient, H1/27/2016
17Hpatient, H2/4/2016
18Hpatient, H1/27/2016
19Hpatient, H1/14/2016
20Hpatient, H1/12/2016
21Hpatient, H1/7/2016
22Hpatient, H1/13/2016
23Hpatient, H2/17/2016
24Hpatient, H2/16/2016
25Hpatient, H1/21/2016
26Hpatient, H2/11/2016
27Hpatient, H1/14/2016
28Hpatient, H1/27/2016
29Hpatient, H2/17/2016
30Hpatient, H2/16/2016
31Hpatient, H2/3/2016
32Hpatient, H1/27/2016
33Hpatient, H2/3/2016
34Hpatient, H1/20/2016
35Hpatient, H2/18/2016
36Hpatient, H1/6/2016
37Hpatient, H1/21/2016
38Ppatient, P1/21/2016
39Ppatient, P2/18/2016
40Ppatient, P2/25/2016
41Ppatient, P1/14/2016
42Ppatient, P2/23/2016
43Ppatient, P2/4/2016
44Ppatient, P2/23/2016
45Ppatient, P1/7/2016
46Ppatient, P2/4/2016
47Ppatient, P1/7/2016
48Ppatient, P1/26/2016
49Ppatient, P2/3/2016
50Ppatient, P1/7/2016
51Ppatient, P1/20/2016
52Ppatient, P1/6/2016
DASHBOARD
Cell Formulas
RangeFormula
D3=SUM(COUNTIF(Export!A2:A500,{"a","b","c","D","E","F","G"}&"*"))
D4=SUM(COUNTIF(Export!A2:A500,{"h","i","j","k","l","m","n"}&"*"))
D5=SUM(COUNTIF(Export!A3:A501,{"o","p","q","r","s","t","u","v","W","X","Y","Z"}&"*"))
E3=SUM(COUNTIFS(Export!A:A,{"a","b","c","d","e","f","g"}&"*",Export!E:E,"<="&TODAY()-30))
G3=SUM(COUNTIFS(Export!A:A,{"a","b","c","d","e","f","g"}&"*",Export!E:E,">"&TODAY()-45))
G4=SUM(COUNTIFS(Export!A:A,{"h","i","j","k","l","m","n"}&"*",Export!E:E,">"&TODAY()-45))
G5=SUM(COUNTIFS(Export!A:A,{"o","p","q","r","s","t","u","v","W","X","Y","Z"}&"*",Export!E:E,">"&TODAY()-45))
 
Upvote 0
I figured it out! With your formula/help of course. I switched the "<" sign (?). THANK YOU Aladin for your help! I really appreciate your input. This forum is great.



Excel 2012
ABCDE
1Kidney Recipient Appointment Dashboard: Days Since Baseline
2# Pts with Baseline# Pts 0-30 days# Pts 30-45 days# Pts >45days
3Russ12255
4Jason247710
5Stephanie15447
DASHBOARD
Cell Formulas
RangeFormula
B3=SUM(COUNTIF(Export!A2:A500,{"a","b","c","D","E","F","G"}&"*"))
B4=SUM(COUNTIF(Export!A2:A500,{"h","i","j","k","l","m","n"}&"*"))
B5=SUM(COUNTIF(Export!A3:A501,{"o","p","q","r","s","t","u","v","W","X","Y","Z"}&"*"))
C3=SUM(COUNTIFS(Export!A:A,{"a","b","c","D","E","F","G"}&"*",Export!K:K,">"&TODAY()-30))
C4=SUM(COUNTIFS(Export!A:A,{"h","i","j","k","l","m","n"}&"*",Export!K:K,">"&TODAY()-30))
C5=SUM(COUNTIFS(Export!A:A,{"o","p","q","r","s","t","u","v","W","X","Y","Z"}&"*",Export!K:K,">"&TODAY()-30))
D3=SUM(COUNTIFS(Export!A:A,{"a","b","c","D","E","F","G"}&"*",Export!K:K,">"&TODAY()-45,Export!K:K,"<"&TODAY()-30))
D4=SUM(COUNTIFS(Export!A:A,{"h","i","j","k","l","m","n"}&"*",Export!K:K,">"&TODAY()-45,Export!K:K,"<"&TODAY()-30))
D5=SUM(COUNTIFS(Export!A:A,{"o","p","q","r","s","t","u","v","W","X","Y","Z"}&"*",Export!K:K,">"&TODAY()-45,Export!K:K,"<"&TODAY()-30))
E3=SUM(COUNTIFS(Export!A:A,{"a","b","c","d","e","f","g"}&"*",Export!K:K,"<"&TODAY()-45))
E4=SUM(COUNTIFS(Export!A:A, {"h","i","j","k","l","m","n"}&"*",Export!K:K,"<"&TODAY()-45))
E5=SUM(COUNTIFS(Export!A:A,{"o","p","q","r","s","t","u","v","W","X","Y","Z"}&"*",Export!K:K,"<"&TODAY()-45))
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
How can I exactly count NETWORKDAYS in multiple date ranges, after a specific date?
Replies
1
Views
497
Tetra201
Tetra201
A
  • Solved
Counting based on 2 criteria 1 being date exactly 3 months today
Replies
1
Views
414
RoryA
RoryA
M
  • Question Question
Running unique count of occurrence on table based on date
Replies
0
Views
387
Minx_Minxy
M
L
  • Question Question
Formula Help with Count Number of Vessel Turnaround Trips monthly based on Criteria
Replies
1
Views
515
Lukma
L
C
  • Question Question
Stock inventory - count unique sequences from barcode
Replies
0
Views
605
ChemistryBMS
C

Forum statistics

Threads
1,226,906
Messages
6,193,592
Members
453,809
Latest member
KMorales

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