Excel Subtraction in Array Formula

Question

a perhaps fairly simple question that I haven't been able to find the answer for in here:

I want to count the instances in this dataset where the duration between the instances start- and end dates are more than six months.

I specifically want to be able to do this in an Array Formula as my intended implementation of this technique is on a much bigger data set that I want to keep as clean as possible, thus no columns with sums or subtractions.

   A            B
StartDate    EndDate
2017-05-01   2017-08-01
2016-07-01   2017-06-01
2015-09-01   2017-06-01
2015-08-01   2015-09-01
2016-02-01   2018-01-01

{=SUM(IF((A:A-B:B)>183,1,0))}

Here's my intuitive try at doing this, which returns a #VALUE error. How do I go about getting this function to return "3" as is the correct answer to what I'm trying to query in this case?

Many thanks!

Answer 1

Your formula logic is absolutely correct. However, you need to take care of following two things.

• You are including all cells and some of them may contain text (e.g. heading) and those will return error which will give final result as #VALUE.

• Start date is smaller than End Date so all subtraction results will be less than or equal to 0.

So if you fix your own formula like below and CTRL+SHIFT+ENTER it then you will get correct results.

=SUM(IF((B1:B6-A1:A6)>183,1,0))

Or alternatively you can use whole column refs (not recommended).

=SUM(IF(IFERROR(B:B-A:A,0)>183,1,0))

Answer 2

Try,

=sumproduct(--(b2:index(b:b, match(1e99, a:a))-a2:index(a:a, match(1e99, a:a))>183))
'alternate
=sumproduct(--(abs(a2:index(a:a, match(1e99, a:a))-b2:index(b:b, match(1e99, a:a)))>183))