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
=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.
Very cool, thank you very much for explaining it to me! I love learning all the different ways to use a formula.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
if you don't want to use DATEDIF, then you can use this regular formula with just Enter

=SUMPRODUCT((D2:D8-C2:C8>0)*(D2:D8-C2:C8<91))

=SUMPRODUCT((D2:D8-C2:C8>91)*(D2:D8-C2:C8<150))
 

susannarae

New Member
Joined
Jan 31, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
if you don't want to use DATEDIF, then you can use this regular formula with just Enter

=SUMPRODUCT((D2:D8-C2:C8>0)*(D2:D8-C2:C8<91))

=SUMPRODUCT((D2:D8-C2:C8>91)*(D2:D8-C2:C8<150))
Wonderful, good to know! I think right now I want to stick with the DATEDIF, as a lot of the grant reporting specifies months and I'd rather not risk approximation if I don't have to, but I will definitely keep that formula in mind for other uses.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,215
Messages
5,623,433
Members
415,974
Latest member
ZorroOP

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