I am trying to setup a sheet to use the SUMIFS formula to look for a partial date within a timestamp which then sums a range based on that match.

The initial data is being input from a Google Form, which only provides timestamps rather than short dates (MM/DD/YYYY) - This means when I'm trying to calculate 'Today's Sales' there are no matches due to my formula looking for the short date.

The formula I'm currently using is:

=SUMIFS('Form Responses 1'!D:D,'Form Responses 1'!$A:$A,$A$1,'Form Responses 1'!$G:$G,A3)

With cell A1 being a reference date that needs to remain changeable (i.e, enter any date needed)

If I change the data in Column A of the 'Form Responses 1' sheet to short dates I can get matches for what i'm looking for, but as this information comes from a google form I cannot manually change it each time I need to search.

So what I'm hoping for is a formula similar to the one above, but instead can use cell A1 as a reference date and find matches for short dates from Column A's timestamps on the 'Form Responses 1' sheet.

I hope I explained that correctly, but here is an example google sheet of what i'm using:

https://docs.google.com/spreadsheets/d/1A64a9A9Jdjru-1dEIn09togGbPYjTMbFfpwtUoVUGKc/edit#gid=0

CodePudding user response：

I think it works now! Put a DateValue() arround the dates in A:A and put the entire formula in a Arrayformula in order to work :D the formula for B3 is =Arrayformula( SUMIFS('Form Responses 1'!D:D,DATEVALUE( 'Form Responses 1'!$A:$A ),$A$1,'Form Responses 1'!$G:$G,$A3)) cheers

PS (also i Put an $ in the end (A3 --> $A3) so it can easily change columns)