Within a SUMIFS statement, can you extract the hour from a timestamp and use it as criteria?

Darren Morgan

Hello all,

 

I have a problem that has been driving me mad - I would be grateful of some help!

 

I have a spreadsheet where:

Column A contains names (text string)

Coumn B contains costs (numbers)

Colum C contains a timestamp in the format dd/mm/yy hh:mm:ss

 

I want to be able to create a formula that says "add up all the costs from Column B, where Column A contains 'DAR', and the hour is less than 12pm regardless of what the day/date is"

 

I used the following fomula, but I keep getting a 0 returned:

=SUMIFS(B:B,A:A,"*DAR*",C:C,HOUR(C:C)<12)

 

The dates in the C column are across multiple days, but essentially I am trying to 'ignore' the days and add up all costs that happen before 12pm - irresepctive of what day they happen on.

 

I also tried using a SUMPRODUCT statement, but that equally drove me to the brink of insanity.

 

I would be most grateful if someone could help.  I have a tigh deadline coming up, so any help as soon as possible would be much appreciated!

 

Thanks and kind regards

 

Darren

 

 

 

 

 

Shaine Greenwood

Hello Darren,

 

We currently don't have a SUMPRODUCT formula in Smartsheet, and there isn't a way to work with time values (we don't have an HOUR formula) but I've got your vote down for this on our enhancement request list for further consideration.

 

More information on our available functions in Smartsheet can be found in our Help Center: https://help.smartsheet.com/articles/775363-using-formulas

J. Craig Williams

Darren,

 

This formula should solve your problem:

 

=SUMIFS(B:B, A:A, FIND("DAR", @cell) > 0, C:C, LEFT(RIGHT(@cell, 8), 2) < 12)

 

assuming column C is strictly formatted.

 

Craig