CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulasumifs
Emily Prior
Emily Prior

Reputation: 35

How to sum specific information w/ multiple criteria including dates from form submission

I have a sheet that is linked to a google form so when a person submits the form, the information is populated into the sheet automatically with a timestamp, ex.1/17/2020 17:26:16. I'm trying to sum information based on multiple criteria and one is to only pull a full days worth of data but the formula is reading time as well and so I keep yielding 0.

For example, here is some data

1/8/2020 17:38:49   Danny   PM  Beetlejuice on Broadway 1144
1/8/2020 17:38:49   Danny   PM  Oklahoma! on Broadway   1181
1/8/2020 17:38:49   Danny   PM  Oklahoma! on Broadway   1000.5
1/8/2020 12:47:18   Jeff    PM  To Kill a Mockingbird   1675
1/8/2020 12:48:19   Jeff    PM  Jagged Little Pill      2390

On another tab I'm trying to calculate how much was spent by each person on this day. This new tab is looking at a persons shift and name to sumifs their spend:

=SUMIFS('Form Responses 1'!$E:$E,'Form Responses 1'!$D:$D,B$5,'Form Responses 1'!$B:$B,$A9,'Form Responses 1'!$C:$C,$B$2)

I don't believe you'll need to know what each piece in this current code means since I just need to add to it for it to read a range of dates and narrow down to one day.

I've tried adding the date range 'Form Responses 1'!$E:$E and having the criterion be the desired date filled in B2 but this is when it is reading for an exact match of the time from the range which is not going to work since I don't want it to read the time. I want to find a solution that won't involve having to manually update the submission data each time.

I've included a sample sheet here so whoever wants to try and tackle this can better see what it is I'm working with. In the review tab I have my current formula not specifying date and next to it the same but trying to specify the date.

Thank you in advance. My brain is a scattered mess so I hope everything makes sense.

Upvotes: 2

Views: 149

Answers (3)

MattKing
MattKing

Reputation: 7773

Another query that should work for you i've left on the new MK.Help tab in cell A5.

=ARRAYFORMULA(QUERY({INT('Form Responses 1'!A:A),'Form Responses 1'!B1:E},"select Col2,SUM(Col5) where Col1="&B1&" and Col3='"&B2&"' group by Col2 order by SUM(Col5) desc label SUM(Col5)'Total'"))

Agree with the previous poster that a query is the way to go since it'll autopopulate. Also it allows you to display the table in any order you like. I chose to sort by the total with the highest totals at the top.

Upvotes: 0

marikamitsos
marikamitsos

Reputation: 10573

In addition to the accepted correct answer, you could also try the following QUERY formula so you can get everything with just one formula instead of 5.

=IFERROR({QUERY(A:E,"select B, sum(E) where not A='' and C='"&H2&"' group by B label B 'Runner Name', sum(E) 'Total Spend' ",1), 
          QUERY(A:E,"select    sum(E) where not A='' and C='"&H2&"' and todate(A)=date '"&text(H1,"yyyy-mm-dd")&"' group by B label sum(E) 'Totals per day' ",1)},
         "No data")

(Please adjust ranges to your needs)

enter image description here

By using todate(A) we extract the date value from a timestamp.

The big advantage of using a single query is that -since you use the data from a form- your results will auto update as new answers come through.

Please feel free to ask if you need further information.

Upvotes: 0

basic
basic

Reputation: 11968

If you want all records for the specified day to be included, you must use the >= and <= operators.

Something like this:

=SUMIFS('Form Responses 1'!$E:$E,
  'Form Responses 1'!$B:$B, $A6,
  'Form Responses 1'!$C:$C, $B$2,
  'Form Responses 1'!$A:$A, ">="&$B$1,
  'Form Responses 1'!$A:$A, "<="&$B$1+1)

Upvotes: 1

Related Questions