Using DATEDIF in a SUMPRODUCT formula with blank cells

susannarae

New Member
Joined
Jan 31, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

sriche01

Board Regular
Joined
Dec 24, 2013
Messages
136
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 31, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 24, 2013
Messages
136
Office Version
  1. 365
Platform
  1. Windows
So another shortcut, if you can have another column, would be to have a helper column calculate your days for each line
Rich (BB code):
=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

New Member
Joined
Jan 31, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

So another shortcut, if you can have another column, would be to have a helper column calculate your days for each line
Rich (BB code):
=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

Well-known Member
Joined
Oct 2, 2008
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
try this array formula using Ctrl+Shift+Enter:
Do not try to put the { } brackets manually.

Book1
CDEFGH
201-02-202001-04-20207
301-02-202001-04-2020
401-02-202001-04-2020
501-02-2020
601-02-202001-04-2020
701-02-202001-04-2020
801-02-202001-04-2020
Sheet2
Cell Formulas
RangeFormula
H2H2{=SUMPRODUCT(--IFERROR(DATEDIF(C2:C8,D2:D8,"d")<90,1))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

susannarae

New Member
Joined
Jan 31, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Book1
CDEFGH
201-02-202001-04-20207
301-02-202001-04-2020
401-02-202001-04-2020
501-02-2020
601-02-202001-04-2020
701-02-202001-04-2020
801-02-202001-04-2020
Sheet2
Cell Formulas
RangeFormula
H2H2{=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

Well-known Member
Joined
Oct 2, 2008
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
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))
 
Last edited:

susannarae

New Member
Joined
Jan 31, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,751
Something like this maybe:

Book1
ABCDEFGHI
190 days or less3-5 months6-12 months13-17 months18-24 months24+ months
2EnterExit1-0009000091-0015200153-0036500366-0054800549-0073000731-99999
31/1/19902/1/1990202101
42/2/19912/8/1991
53/3/2018
64/4/20154/4/2016
75/5/20165/5/2019
86/6/2000
91/1/20198/1/2019
101/1/20198/7/2019
Sheet11
Cell Formulas
RangeFormula
D3:I3D3=SUMPRODUCT(--($A3:$A21<>""),--($B3:$B21<>""),--($B3:$B21-$A3:$A21<RIGHT(D2,5)+0))-SUM($C3:C3)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,505
Messages
5,625,199
Members
416,080
Latest member
blemon

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
Top