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

[hannaboesch]

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

 

[Aladin Akyurek]

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

 

[hannaboesch]

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. =/

Apatient, A	1/19/2016
Apatient, A	1/21/2016
Apatient, A	2/2/2016
Apatient, A	2/4/2016
Apatient, A	1/27/2016
Apatient, A	2/17/2016
Apatient, A	1/19/2016
Apatient, A	2/11/2016
Apatient, A	1/7/2016
Apatient, A	2/2/2016
Apatient, A	1/21/2016
Apatient, A	2/3/2016
Apatient, A	2/11/2016
Apatient, A	1/14/2016
Apatient, A	1/27/2016
Apatient, A	2/4/2016
Apatient, A	1/27/2016
Apatient, A	1/14/2016

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Instead of reporting what the formula returns, you should say what the result must be for the data set you posted.

 

[hannaboesch]

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.

 

[Aladin Akyurek]

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.

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.

 

[hannaboesch]

Excel 2012
	A	B	C	D	E	F	G
1	Patient Name	Baseline Date
2	Apatient, A	1/19/2016		# Pts with Baseline	# Pts 0-30 days	# Pts 30-45 days	# Pts >45days
3	Apatient, A	1/21/2016	A-G	12	10		7
4	Apatient, A	2/2/2016	H-N	24			14
5	Apatient, A	2/4/2016	O-Z	15			8
6	Apatient, A	1/27/2016
7	Apatient, A	2/17/2016
8	Apatient, A	1/19/2016
9	Apatient, A	2/11/2016
10	Apatient, A	1/7/2016
11	Apatient, A	2/2/2016
12	Apatient, A	1/21/2016
13	Apatient, A	2/3/2016
14	Hpatient, H	2/11/2016
15	Hpatient, H	1/14/2016
16	Hpatient, H	1/27/2016
17	Hpatient, H	2/4/2016
18	Hpatient, H	1/27/2016
19	Hpatient, H	1/14/2016
20	Hpatient, H	1/12/2016
21	Hpatient, H	1/7/2016
22	Hpatient, H	1/13/2016
23	Hpatient, H	2/17/2016
24	Hpatient, H	2/16/2016
25	Hpatient, H	1/21/2016
26	Hpatient, H	2/11/2016
27	Hpatient, H	1/14/2016
28	Hpatient, H	1/27/2016
29	Hpatient, H	2/17/2016
30	Hpatient, H	2/16/2016
31	Hpatient, H	2/3/2016
32	Hpatient, H	1/27/2016
33	Hpatient, H	2/3/2016
34	Hpatient, H	1/20/2016
35	Hpatient, H	2/18/2016
36	Hpatient, H	1/6/2016
37	Hpatient, H	1/21/2016
38	Ppatient, P	1/21/2016
39	Ppatient, P	2/18/2016
40	Ppatient, P	2/25/2016
41	Ppatient, P	1/14/2016
42	Ppatient, P	2/23/2016
43	Ppatient, P	2/4/2016
44	Ppatient, P	2/23/2016
45	Ppatient, P	1/7/2016
46	Ppatient, P	2/4/2016
47	Ppatient, P	1/7/2016
48	Ppatient, P	1/26/2016
49	Ppatient, P	2/3/2016
50	Ppatient, P	1/7/2016
51	Ppatient, P	1/20/2016
52	Ppatient, P	1/6/2016
DASHBOARD

Cell Formulas
Range	Formula
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))

 

[Aladin Akyurek]

What should be the values in D3:G5?

 

[hannaboesch]

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
	A	B	C	D	E
1		Kidney Recipient Appointment Dashboard: Days Since Baseline
2		# Pts with Baseline	# Pts 0-30 days	# Pts 30-45 days	# Pts >45days
3	Russ	12	2	5	5
4	Jason	24	7	7	10
5	Stephanie	15	4	4	7
DASHBOARD

Cell Formulas
Range	Formula
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))

 

[Aladin Akyurek]

That's great to hear. You are welcome.