SUMPRODUCT help needed

[RobertK]

I have a spreadsheet with about 1000 rows. In rows M & N there are dates. I
am trying to add in on e step the differences in the dates in column M & N
using the formula
=SUMPRODUCT(--($N$5:$N$1500>$M$5:$M$1500),--($M$5:$M$1500<>""),($N$5:$N$1500-$M$5:$M$1500))
This gives me a sum of 162,720. However to check the accuracy of the
formula I calculated the added the difference between each row and totaled
the numbers, that gave me an answer of 159,320. If column has a date column
B doesn’t necessarily have one and vice versa. Rows with only one date shown
are ignored. Also some rows have no dates. 98% of the time column Nis
greater than column N but there are some entries where N is larger than M.
I’m thinking that might be the problem. It’s alright if N is larger than M.

 

[Luke M]

You SUMPRODUCT is only adding numbers where N > M. However, when you did a
check, you prb did a straight subtraction of N - M. For the rows where N < M,
this results in a negative number, thus leading to a smaller answer than the
one returned by SUMPRODUCT.

If it doesn't matter if N is bigger than M, remove that arguement from the
SUMPRODUCT function.

 

[Pete_UK]

This will only subtract M from N if N is greater than M. Look at the
response I gave you the other day on earlies and lates. If you are not
bothered about negative days, then the first term is not needed,
although you might want a term to check that column N is not blank,
like this:

=SUMPRODUCT(--($N$5:$N$1500<>""),--($M$5:$M$1500<>""),($N$5:$N$150­0-$M
$5:$M$1500))

This should give you the answer you expect.

Hope this helps.

Pete

 

[RobertK]

Luke/Pete, thanks that did it.
--
Robert K

This will only subtract M from N if N is greater than M. Look at the
response I gave you the other day on earlies and lates. If you are not
bothered about negative days, then the first term is not needed,
although you might want a term to check that column N is not blank,
like this:

=SUMPRODUCT(--($N$5:$N$1500<>""),--($M$5:$M$1500<>""),($N$5:$N$150­0-$M
$5:$M$1500))

This should give you the answer you expect.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome, Robert - glad to hear it.

Pete

Luke/Pete, thanks that did it.  
--
Robert K






- Show quoted text -