Using DATEDIF in a SUMPRODUCT formula with blank cells

[susannarae]

Hello all,

I do a lot of work in Excel for my company's home visiting programs, which requires a lot of grant reporting and summaries. I'm trying to create a formula that will make my life easier in that regard. One of the requirements for grant reporting is reporting on how many clients were enrolled for a certain period of time. The ranges are:
-90 days or less
-91 days-5 mths
-6mths-12mths
-13mths-17mths
-18mths-24mths
-24+ mths

I have on my spreadsheet the clients' enrollment dates in column C, and their exit dates in column D. Right now I've been working on just the basic 90 days or less, and my formula looks like this: =SUMPRODUCT((--(DATEDIF(C2:C8,D2:D8,"d")<90))). This formula works great--until I there is a blank cell in D and a date in C (meaning a client is still enrolled). Then a "#NUM!" error pops up, which leads me to think that it's something to do with the DATEDIF formula (as that error shows when the end date is earlier than the begin date). I'm hoping that you all will be able to help; I've been working on it for the past two days and I'm ready to pull my hair out!

 

[sriche01]

A quick thought... could current enrollees have the TODAY function in the outward parameter so it has a date in it, or would that mess other things up?

 

[susannarae]

A quick thought... could current enrollees have the TODAY function in the outward parameter so it has a date in it, or would that mess other things up?

Unfortunately it would. I need to know which clients are active as well, and the only reliable way to track that is through the Exit column. Not only that, this particular spreadsheet is used by the whole program for inputting their client info, and I've no doubt that adding that would be very confusing for them.

 

[sriche01]

So another shortcut, if you can have another column, would be to have a helper column calculate your days for each line

=IF(ISNUMBER(D2),D2-C2,TODAY()-C2)

Copy the above formula, paste it into an available column in row 2 and copy down. Then a COUNTIFS function would add up the lines based on the number of days in that column.

I'll keep trying for a formula to go without the helper column if that isn't an option.

 

[susannarae]

So another shortcut, if you can have another column, would be to have a helper column calculate your days for each line

=IF(ISNUMBER(D2),D2-C2,TODAY()-C2)

Copy the above formula, paste it into an available column in row 2 and copy down. Then a COUNTIFS function would add up the lines based on the number of days in that column.

I'll keep trying for a formula to go without the helper column if that isn't an option.

I have considered adding a helper column, but I'm worried that one of the people inputting client information will somehow delete the formula! Not only that, it's already a pretty large spreadsheet, adding another column will just make it that much bigger and (to my mind) more difficult to easily navigate for the direct care workers who aren't accustomed to Excel and its workings.

I greatly appreciate your input though! If worst comes to worst, I'll add one. I just hate to do it when I'm almost positive there's an easier way!

 

[snjpverma]

try this array formula using Ctrl+Shift+Enter:
Do not try to put the { } brackets manually.

Book1
	C	D	E	F	G	H
2	01-02-2020	01-04-2020				7
3	01-02-2020	01-04-2020
4	01-02-2020	01-04-2020
5	01-02-2020
6	01-02-2020	01-04-2020
7	01-02-2020	01-04-2020
8	01-02-2020	01-04-2020
Sheet2

Cell Formulas
Range	Formula
H2	H2	{=SUMPRODUCT(--IFERROR(DATEDIF(C2:C8,D2:D8,"d")<90,1))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[susannarae]

try this array formula using Ctrl+Shift+Enter:
Do not try to put the { } brackets manually.

Book1
	C	D	E	F	G	H
2	01-02-2020	01-04-2020				7
3	01-02-2020	01-04-2020
4	01-02-2020	01-04-2020
5	01-02-2020
6	01-02-2020	01-04-2020
7	01-02-2020	01-04-2020
8	01-02-2020	01-04-2020
Sheet2

Cell Formulas
Range	Formula
H2	H2	{=SUMPRODUCT(--IFERROR(DATEDIF(C2:C8,D2:D8,"d")<90,1))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Well it's not popping up with the #NUM! error, which seems to be a step in the right direction! However, it appears to be automatically tallying the now-blank cells into the total; f I have 4 clients with less than 90 days in the program, and 2 clients with blank cells in the exit date, the total reads as 6. Any ideas?

 

[snjpverma]

I thought you wanted to add the blanks as well.

Just change the last digit "1" to "0" as highlighted in red in below formula and confirm with ctrl shift and enter as you did previously.

=SUMPRODUCT(--IFERROR(DATEDIF(C2:C8,D2:D8,"d")<90,0))

 

[susannarae]

I thought you wanted to add the blanks as well.

Just change the last digit 1 to "0" as highlighted in red in below formula and confirm with ctrl shift and enter as you did previously.

=SUMPRODUCT(--IFERROR(DATEDIF(C2:C8,D2:D8,"d")<90,0))

It works, thank you so much! I'm guessing if I want to add the additional parameters (between 91 days and 5 months, for example), I would just need to tack on a secondary formula after a * to link them? For example, {=SUMPRODUCT(--IFERROR(DATEDIF(C2:C8,D2:D8,"d")>91,0)*(--IFERROR(DATEDIF(C2:C8,D2:D8,"d")<150)}?

 

[Eric W]

Something like this maybe:

Book1
	A	B	C	D	E	F	G	H	I
1				90 days or less	3-5 months	6-12 months	13-17 months	18-24 months	24+ months
2	Enter	Exit		1-00090	00091-00152	00153-00365	00366-00548	00549-00730	00731-99999
3	1/1/1990	2/1/1990		2	0	2	1	0	1
4	2/2/1991	2/8/1991
5	3/3/2018
6	4/4/2015	4/4/2016
7	5/5/2016	5/5/2019
8	6/6/2000
9	1/1/2019	8/1/2019
10	1/1/2019	8/7/2019
Sheet11

Cell Formulas
Range	Formula
D3:I3	D3	=SUMPRODUCT(--($A3:$A21<>""),--($B3:$B21<>""),--($B3:$B21-$A3:$A21<RIGHT(D2,5)+0))-SUM($C3:C3)