Sumproduct and datedif #num error

[NX100]

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

 

[steve the fish]

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.

 

[DRSteele]

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.

	A	B	C	D	E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]base:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]May-17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]net(d)[/TD]
[TD="bgcolor: #FFF2CC"]datedif("d")[/TD]
[TD="bgcolor: #FFF2CC"]datedif("m")[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Jan-17[/TD]
[TD="align: right"]-120[/TD]
[TD="align: right"]120[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Feb-17[/TD]
[TD="align: right"]-89[/TD]
[TD="align: right"]89[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Mar-17[/TD]
[TD="align: right"]-61[/TD]
[TD="align: right"]61[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Apr-17[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]May-17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]0[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Jun-17[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]1[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Jul-17[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]61[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]2[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Aug-17[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]92[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]3[/TD]

</tbody>

Sheet27​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]=B4-$B$1[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]=DATEDIF(MIN($B$1,B4),MAX($B$1,B4),"d")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E4[/TH]
[TD="align: left"]=DATEDIF(MIN($B$1,B4),MAX($B$1,B4),"m")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[NX100]

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

 

[NX100]

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!