Sumproduct and datedif #num error

NX100

New Member
Joined
Sep 26, 2004
Messages
5
Hi,

I'm working on an excel function after many years away and I have come a little unstuck. I understand the problem I am just struggling to find a solution to it and would appreciate any help anyone might be able to offer.


The function is straightforward

<code>
=SUMPRODUCT(--(DATEDIF(People!$H2:$H1000,AB$1,"m")=1))
</code>

The people sheet has a range of dates on it

e.g.

Jan-18
Mar-18
Jun-18
Aug-19


AB$1 is a date e.g Jun-18

The issue I have is that DATEDIF throws a #num error if the start date is greater than the end date. This causes the error to permeate up through the SUMPRODUCT. Is there a way to protect against the error within the SUMPRODUCT or switch the DATEDIF around dynamically.

e.g. I would do this outside of a SUMPRODUCT when working with DATEDIF

<code>
=IFERROR(DATEDIF($H2:$H2000,$AB1,"M"),DATEDIF($AB1,$H2:$H2000,"M")
</code>

Any help welcome

Thanks
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
These two countifs calculate the same if that helps:

=COUNTIFS(H1:H1000,">="&EDATE(AB1,1),H1:H1000,"<"&EDATE(AB1,2))+COUNTIFS(H1:H1000,"<"&EDATE(AB1,-1),H1:H1000,">="&EDATE(AB1,-2))

but you could use your iferror but must enter CSE.
 
Upvote 0
I assume you want the number of whole months between two dates. Then you want test whether each is 1 month away, and to count the number of dates that meet that criteria.

Let's first see if this will get us on the right track.
Copy formulas in row4 downwards.

ABCDE
1base:May-17
2
3net(d)datedif("d")datedif("m")
4Jan-17-1201204
5Feb-17-89893
6Mar-17-61612
7Apr-17-30301
8May-17000
9Jun-1731311
10Jul-1761612
11Aug-1792923

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet27

Worksheet Formulas
CellFormula
C4=B4-$B$1
D4=DATEDIF(MIN($B$1,B4),MAX($B$1,B4),"d")
E4=DATEDIF(MIN($B$1,B4),MAX($B$1,B4),"m")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
These two countifs calculate the same if that helps:

=COUNTIFS(H1:H1000,">="&EDATE(AB1,1),H1:H1000,"<"&EDATE(AB1,2))+COUNTIFS(H1:H1000,"<"&EDATE(AB1,-1),H1:H1000,">="&EDATE(AB1,-2))

but you could use your iferror but must enter CSE.

Steve - thanks, this does the job nicely. I want to avoid the CSE if I can help it as this sits as part of a much larger function.

Thanks again for your help :)
 
Upvote 0
DRSteele,

Thanks for your input. Yes, this is an approach. I've gone for Steve the fishes approach of rolling into a single function as I am nesting within a larger function but I appreciate your help here.

Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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
Back
Top