SUMPRODUCT with reformatting "on the fly"

[karate718]

Hello, I am using sumproduct to create a summary table that adds the score for each day. The issue I have is that the time stamp for each day includes the time as well, whereas the summary portion of the spreadsheet has no timestamp. Therefore, the equality is false.

Col F               Col J

10/8/15 12:26:59    0.25
10/8/15 12:29:14    0.25
10/8/15 12:29:21    0

10/9/15 13:07:27    -.75
10/9/15 13:07:27     0
10/9/15 13:07:27    -.75

The formula in T10 is: =SUMPRODUCT(--(F:F=S10),J:J) where S10 contains 10/8/15 as a date. The result should be 0.5.
The formula in T11 is: =SUMPRODUCT(--(F:F=S11),J:J) where S11 contains 10/9/15 as a date. The result should be -1.5.

Is there a way to convert column F to dates only within the sumproduct formula? I do not want to create another column in the data table itself which holds only the date. Also, I'd prefer not to split the date and time into 2 separate columns.

As far as doing the actual conversion, using the INT formula on the Date-Time cell and comparing that to the date cell works great. In other words, INT(10/9/15 13:07:27) = 10/9/15.

To sum up, I'd like something like this: =SUMPRODUCT(--(INT(F:F)=S10),J:J)

Thank you.

 

[Jonmo1]

Try

=SUMPRODUCT(--(INT(F:F)=S10),J:J)

However, I highly recommend NOT using entire column refs like F:F in sumproduct.
Restrict it to the actual used area like F2:F100

 

[Jonmo1]

You could also use SUMIFS if you have XL2007+
This would not be an issue for using entire column refernces.

=SUMIFS(J:J,F:F,">="&S10,F:F,"<"&S10+1)

 

[karate718]

You could also use SUMIFS if you have XL2007+
This would not be an issue for using entire column refernces.

=SUMIFS(J:J,F:F,">="&S10,F:F,"<"&S10+1)

This worked perfectly, thank you!

This did not, =SUMPRODUCT(--(INT(F:F)=S10),J:J). I tried that but it gives a #VALUE! error.

 

[Jonmo1]

This worked perfectly, thank you!

You're welcome.

This did not, =SUMPRODUCT(--(INT(F:F)=S10),J:J). I tried that but it gives a #VALUE! error.

If there are any TEXT values in F (a header row perhaps) than INT(text) = #Value! error.

You would have to restrict the range (as mentioned before) so that there are no TEXT values in column F
So perhaps
=SUMPRODUCT(--(INT(F2:F100)=S10),J2:J100)


But the sumifs is better anyway..

 

[karate718]

You're welcome.


If there are any TEXT values in F (a header row perhaps) than INT(text) = #Value! error.

You would have to restrict the range (as mentioned before) so that there are no TEXT values in column F
So perhaps
=SUMPRODUCT(--(INT(F2:F100)=S10),J2:J100)


But the sumifs is better anyway..

You are 100% correct. I had a header row and the SUMIFS is better in this case.

Thanks again for your help.