SUMPRODUCT includes comparison of date ranges in two columns and equating it to a constant

Hi friends
I have weird situation in hand..
I have 3 columns,
A B C
10/25/2012 0:00 10/25/2012 0:00 1 - Urgent
11/5/2012 0:00 11/8/2012 0:00 4 - Medium
10/23/2012 0:00 4 - Medium
10/25/2012 0:00 3 - High
10/25/2012 0:00 4 - Medium

I have this formula to calculate for A = B and it is fine. The result is 1,
=SUMPRODUCT(--(O:O=J:J),--(O:O<>0),--(G:G="1 - Urgent"))

But I cant modify this to find B - A = 8
i.e I tried this,
=SUMPRODUCT(--(O:O="J:J+3"))--(O:O<>0)--(G:G="1 - Urgent") this doesnt work

Please can you help ?

Appreciate it. Thank you

Try

Formula:

Please Login or Register  to view this content.

Sorry
Had played around with that.

Gets my a #Value error.

But thanks

Can you upload your excel with expected output?

sumproduct.xlsx

Sure. PFA.
TY

Try this formula. I am getting 1 for this in your sheet.

Formula:

Please Login or Register  to view this content.

oh. Sorry, this is a sample data , So the formula you provided will work. But I generally have large data and I dont know the end range of data in a column.

So if I convert your formula to include all the data in a column,
=SUMPRODUCT(--((B:B-A:A)=3),--(B:B<>0),--(C:C="1 - Urgent"))

#Value error

It is because of the headers in the first row. The subtraction operation A1-B1 is resulting that error. The formula will work if you remove it.

If you cannot remove it, select from B2 to last row B65536.

Thats great. Nice to know the reason for value error.
I think I will stick to 65536 untill I hit a data greater than that.

I think I will mark this resolved. TY Sindhus.

just a curious thought. Any idea on eliminating the header?

Try

Formula:

Please Login or Register  to view this content.

Confirmed with ctrl+Shift+Enter