SUMPRODUCT help needed
 

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 doesnt 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.
Im thinking that might be the problem. Its alright if N is larger than M.

--
Robert K

SUMPRODUCT help needed
 

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"RobertK" wrote:

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 doesnt 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.
Im thinking that might be the problem. Its alright if N is larger than M.

--
Robert K

SUMPRODUCT help needed
 

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

On Sep 21, 8:05*pm, RobertK wrote:
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$150*0-$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.

--
Robert K

SUMPRODUCT help needed
 

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


"Pete_UK" wrote:

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

On Sep 21, 8:05 pm, RobertK wrote:
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$150Â*0-$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 doesnt 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..
Im thinking that might be the problem. Its alright if N is larger than M.

--
Robert K

SUMPRODUCT help needed
 

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

Pete

On Sep 21, 10:10*pm, RobertK
wrote:
Luke/Pete, thanks that did it. *
--
Robert K



"Pete_UK" wrote:
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

On Sep 21, 8:05 pm, RobertK wrote:
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$150**0-$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.

--
Robert K- Hide quoted text -

- Show quoted text -