Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Within a SUMIFS statement, can you extract the hour from a timestamp and use it as criteria?
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
Comments
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives