SOLVED

SUMIF formula

Copper Contributor

Hello, 

 

I am trying to do a sumif of and for the criteria I am referencing a column that consist of a time stamp formula: =IF(M18=T18, IF(N18="",NOW(), N18), ""). This formula just says if the order is marked completed insert the day it is marked completed. And so with the sumif I want to reference this table and say if the output date = the date of another table column then add the number of doors. Current formula - =SUMIF(Table1[Completion Date], [@Date],Table1['# of Doors]) - results in 0.

 

Thank you in advance for any help!

6 Replies

@tkdkenney 

=SUMIF(Table1[Completion Date],[@Date],Table1['# of Doors])

 

This formula works in my sheet.

 

sum doors.JPG

@OliverScheurich
I think the problem is my completion date is the output of a formula. If you were to manually enter the completion date, then the formula works.
best response confirmed by Hans Vogelaar (MVP)
Solution

@tkdkenney 

Please try to change your timestamp formula a little bit.

 

=IF(M18=T18, IF(N18="",INT(NOW()), N18),"")

 

Or something simpler like @Hans Vogelaar correctly wrote:

=IF(M18=T18, IF(N18="",TODAY(), N18),"")

 

The NOW() function returns the date and time. The time is the decimal part. Therefore, when comparing with date, you should only use the integer part of NOW().

@dscheikey
Thank you! That has fixed it!

@dscheikey 

Why not use TODAY() instead of INT(NOW()) ?

@Hans Vogelaar 

Absolutely right. I hadn't thought of that.

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@tkdkenney 

Please try to change your timestamp formula a little bit.

 

=IF(M18=T18, IF(N18="",INT(NOW()), N18),"")

 

Or something simpler like @Hans Vogelaar correctly wrote:

=IF(M18=T18, IF(N18="",TODAY(), N18),"")

 

The NOW() function returns the date and time. The time is the decimal part. Therefore, when comparing with date, you should only use the integer part of NOW().

View solution in original post