Help with Sumproduct function

[atandon]

Hi All,

I have a peculiar problem for which I need help. Please consider the following:

Column A has Dates
Column B has Dates

Now I want to count all rows where:
1> Difference between the two dates is greater than 2 days &
2> Date in Column A is greater than a give date (Say in cell C1)
3> Values in Column D do not contain a string

Now I know this can be done with Sumproduct but am finding it hard to get the exact working formula

Can someone help please

=SUMPRODUCT(--((A3:A9999-B3:B9999)>2),--(A3:A9999>C1),--(D3:D9999<>"In Progress"))

[daddylonglegs]

Your formula looks valid, doesn't it work? Although in normal circumsatnces, if col B dates are later than Col A your subtraction would be the other way round, i.e.

=SUMPRODUCT(--(B3:B9999-A3:A9999>2),--(A3:A9999>C1),--(D3:D9999<>"In Progress"))

[XOR LX]

Hi,

I see absolutely nothing wrong with your formula given those criteria, apart from perhaps: "the difference between the two dates is greater than 2 days", for which you haven't defined which date should be the greater.

Perhaps you can post a sample workbook illustrating where you think it fails?

Regards

[martindwilson]

that works just as it is providing dates in col b are earlier than column a, which you could reverse if needed or use abs
=SUMPRODUCT(--(ABS(A3:A9999-B3:B9999)>2),--(A3:A9999>C1),--(D3:D9999<>"In Progress"))

[atandon]

Sadly this doesn't work for me

[XOR LX]

What doesn't work?

Post a sample workbook, please.

Regards

[sktneer]

If A3 is greater then

=SUMPRODUCT(--(A3:A9999-B3:B9999>2),--(A3:A9999>C1),--(D3:D9999<>"In Progress"))


If B3 is greater then

=SUMPRODUCT(--(B3:B9999-A3:A9999>2),--(A3:A9999>C1),--(D3:D9999<>"In Progress"))

[atandon]

Ok...I think I found the issue...

Basically there was some text in some rows which was skewing the output...is there any chance that I can add one more criteria to remove that?

Column B should not contain invalid dates
Date if present in Column B should be greater than 2 than that in Column A
Date in Column D should be greater than C1

eg.
=SUMPRODUCT(--(B3:B9999<>""),--(B3:B9999<>"In Progress"),--(B3:B9999-A3:A9999>2),--(D3:D9999>C1))

Cheers

[martindwilson]

maybe this array entered formula will do the trick as blank-blank =0
blank -date will be -ve so less than 0
date -text would be #value which the formula ignores
=COUNT(IF(ISNUMBER(B1:B100-A1:A100),IF(B1:B100-A1:A100>2,B1:B100-A1:A100)))

[atandon]

Thanks a lot.

This one worked like a charm

Cheers,
Anubhav