0

I need help getting to show the result of a dropdown. If you look at C22, there's a dropdown for a specific date. I'm currently working on row 24 and have applied the formulas I think should work. I got it to show the coins from May 25, but cannot get the Duration, Days, MTD Coins and MTD Hours for May 25 to show.

https://docs.google.com/spreadsheets/d/1XJyPEqNMxi2EhzSiUYpYOBMv5m59HD2dLx_Y7rFtGMs/edit?resourcekey#gid=958394600

Here's what should happen. Selecting the date in C22 should display "Laugene's" coins, Duration, Days, MTD Coins and MTD Hours.

Issues encountered (Row 24):

  1. Duration, Days, MTD Coins and MTD Hours are not showing.
  2. Total Duration and Days are not summing up on Col BN and BM

Any thoughts or suggestions?

4
  • Welcome to Web Applications. While including a link to a demo spreadsheet usually is helpful, the question should be self-standing, so, please add the formula that you tried and some sample data read by it directly into the question body. May 23, 2021 at 22:09
  • Ody, your intent is not clear. I see that you are choosing a date in Row 22. But what does that date mean? What is it supposed to do? Do you want to see the Coins and Duration for that day only? If so, then "Days" doesn't make sense, since it is only one day being requested (or do you want to show either 1 or 0, meaning they either logged more than an hour or they did not)? And the two MTD columns are also unclear against a chosen date. Do you want to see Coins and Duration for the month ONLY up to the chosen date in Row 22?
    – Erik Tyler
    May 24, 2021 at 1:57
  • In addition, your sheet layout does not make much sense. For instance, you should not have a summary chart below your top chart unless you are sure you will never have more than 17 users (i.e., filling up to row 20). You've got merged cells, which you should not use, since they nearly always cause problems in calculations. You have circular reference issues. Your sheet name include "JUN" while the dates in the sheet are "May." And you have hundreds of formulas where you only need 10 or so. I feel that unraveling all the problems in the sheet would take a long time, even for an expert.
    – Erik Tyler
    May 24, 2021 at 1:59
  • The table in JUN'21 ODY!A1:BO20 is a report rather than a data table. You may want to use drop-downs to select the contents to display in the report rather than creating a new sheet for every month. There are easier ways to create the report, but that is off-topic in this question — you may want to post another question regarding that. Note that the formulas in the answer below read the data directly from Form Responses 1 which is easier than trying to parse the report in A1:BO20. May 24, 2021 at 3:55

1 Answer 1

0

The query() function has a limitation that prevents the summing of date, time and duration values. The workaround is to use value() to convert such datetime values to pure numbers before aggregation. The result will be a dateserial number. You can format the result cell as a datetime to show the values in a readable format.

To get the names, coins and durations for the date selected in B22:D22, clear the range A23:C25 and put this formula in cell A23:

=arrayformula( 
  query( 
    { 
      'Form Responses 1'!A1:E, 
      value('Form Responses 1'!F1:F), 
      'Form Responses 1'!G1:G, 
      text('Form Responses 1'!E1:E, "mmmmddyyyy") 
    }, 
    "select Col2, sum(Col4), sum(Col6) 
     where Col2 is not null 
     and Col8 = '" & B22 & C22 & D22 & "' 
     group by Col2 
     label sum(Col4) 'Coins', sum(Col6) 'Duration' ", 
    1 
  ) 
)

To get days, MTD coins and MTD hours, put these formulas in D24:F24 and fill them down:

=countunique( 
  iferror( 
    filter( 
      'Form Responses 1'!E$2:E, 
      text('Form Responses 1'!E$2:E, "mmmmyyyy") = B$22 & D$22, 
      'Form Responses 1'!B$2:B = A24 
    ) 
  ) 
)
=sum( 
  iferror( 
    filter( 
      'Form Responses 1'!D$2:D, 
      text('Form Responses 1'!E$2:E, "mmmmyyyy") = B$22 & D$22, 
      'Form Responses 1'!B$2:B = A24 
    ) 
  ) 
)
=sum( 
  iferror( 
    filter( 
      value('Form Responses 1'!F$2:F), 
      text('Form Responses 1'!E$2:E, "mmmmyyyy") = B$22 & D$22, 
      'Form Responses 1'!B$2:B = A24 
    ) 
  ) 
)

See query() and filter().

See this answer for an explanation of how date and time values work in spreadsheets.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.