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!
 

susannarae

New Member
Joined
Jan 31, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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)}?
Actually, it ALMOST works. For some reason even when all 6 trial dates are over 90 days of enrollment, the number still tallies as 1. If there is a blank cell in the exit, then that number switches to 0. Is there an easy fix?
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

susannarae

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

I tried this out too, and it does work! I'm not 100% sure of the breakdown though, so replicating it will be tricky for me. Still, it's another good way to look at things, thank you!
 

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
Don't just use enter
Use control + shift and enter and the formula will give correct result.

Also, I see that you haven't given any value to the 2nd IFERROR function

See below correction in red.

=SUMPRODUCT(IFERROR(DATEDIF(C2:C8,D2:D8,"d")>91,0)*IFERROR(DATEDIF(C2:C8,D2:D8,"d")<150,0))

Actually, it ALMOST works. For some reason even when all 6 trial dates are over 90 days of enrollment, the number still tallies as 1. If there is a blank cell in the exit, then that number switches to 0. Is there an easy fix?
 

susannarae

New Member
Joined
Jan 31, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Don't just use enter
Use control + shift and enter and the formula will give correct result.

Also, I see that you haven't given any value to the 2nd IFERROR function

See below correction in red.

=SUMPRODUCT(IFERROR(DATEDIF(C2:C8,D2:D8,"d")>91,0)*IFERROR(DATEDIF(C2:C8,D2:D8,"d")<150,0))
That's what I thought I did, but I tried it again and now it works! Thank you so much for your help, and for your corrections on my parameters!
 

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

That's what I thought I did, but I tried it again and now it works! Thank you so much for your help, and for your corrections on my parameters!

Glad to help. Thanks for the feedback.

Actually, you can use a single IFERROR function for both the conditions as shown below.

=SUMPRODUCT(IFERROR((DATEDIF(C2:C8,D2:D8,"d")>91)*(DATEDIF(C2:C8,D2:D8,"d")<150),0))
 

susannarae

New Member
Joined
Jan 31, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Don't just use enter
Use control + shift and enter and the formula will give correct result.

Also, I see that you haven't given any value to the 2nd IFERROR function

See below correction in red.

=SUMPRODUCT(IFERROR(DATEDIF(C2:C8,D2:D8,"d")>91,0)*IFERROR(DATEDIF(C2:C8,D2:D8,"d")<150,0))
Okay, last question, I hope! There are some cells that are left blank for both Entry and Exit (clients referred but whose cases aren't open yet). I thought that those cells would automatically be ignored, since they're both blank, but it looks like that's not the case. Can I just use the IFERROR formula again for the Entry cells (Column C)?
 

susannarae

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

ADVERTISEMENT

Okay, last question, I hope! There are some cells that are left blank for both Entry and Exit (clients referred but whose cases aren't open yet). I thought that those cells would automatically be ignored, since they're both blank, but it looks like that's not the case. Can I just use the IFERROR formula again for the Entry cells (Column C)?
I want to clarify too that the blank C and D columns are ignored with the parameters for 91d-3m, but are not ignored for <90 days.
 

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
Since zero is less than 90 it is considering it as a TRUE condition. So you need to add a condition for checking if it is more than 0.

try this


Book1
CDEF
201-02-202001-03-2020
3
43
5
6
701-02-202001-03-2020
801-02-202001-03-2020
Sheet2
Cell Formulas
RangeFormula
F4F4{=SUMPRODUCT(IFERROR((DATEDIF(C2:C8,D2:D8,"d")>0)*(DATEDIF(C2:C8,D2:D8,"d")<90),0))}
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
try this:

Book1
CDEF
201-02-202001-03-2020
3
43
5
6
701-02-202001-03-2020
801-02-202001-03-2020
Sheet2
Cell Formulas
RangeFormula
F4F4{=SUMPRODUCT(IFERROR((DATEDIF(C2:C8,D2:D8,"d")>0)*(DATEDIF(C2:C8,D2:D8,"d")<90),0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
Wonderful, thank you so much! I really appreciate everyone's help on this! :D
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,739
I tried this out too, and it does work! I'm not 100% sure of the breakdown though, so replicating it will be tricky for me. Still, it's another good way to look at things, thank you!

=SUMPRODUCT(--($A3:$A21<>""),--($B3:$B21<>""),--($B3:$B21-$A3:$A21<RIGHT(D2,5)+0))-SUM($C3:C3)

The red part makes sure there's a value in the A column, the blue part makes sure there's a value in the B column, the bold green part takes the last 5 characters from the row above it and coverts it to a number (days), the rest of the green makes sure that the difference between A and B is less than that number. If that number is 365 for example, it will count all the other groups less than that too. So the SUM part on the end subtracts out the clients who have already been counted.

I converted it to use days, since that's generally easier than converting by months.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,946
Messages
5,621,770
Members
415,856
Latest member
jimb2k

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