0

I am using this formula in Google Sheets:

=sumifs('Fieldwork Log'!M:M,'Fieldwork Log'!G:G, ">="&date(2022,8,1),'Fieldwork Log'!G:G,"<="&date(2022,8,31))

It returns the sum of time durations for each month. Here is a link to the sheet: Fieldwork Tracker
I would like to drag the formula down a column but have the dates inside the ">="&date(2022,8,1) and "<="&date(2022,8,31)) move forward chronologically. Can anyone help? Cant find any answers anywhere on the web and I have others using the worksheet on their own with zero experience with spreadsheets.

5
  • @darkdiamond thank you for your suggestion, new user here.
    – Anthony
    Jul 28 at 19:25
  • you're welcome! This is what the review queue for first questions is for. Accept from that, your question is well asked.
    – DarkDiamond
    Jul 28 at 19:30
  • I think I just solved my own problem. I can reference the date from a list that shouldnt take long to make.
    – Anthony
    Jul 28 at 19:32
  • Then please post your solution as an answer to your own question so that everyone else that comes here can solve the problem as well
    – DarkDiamond
    Jul 28 at 19:35
  • Instead of the formula having the numbered date e.g.,(2022,8,1), create 6 columns for each (y,m,d)1 and (y,m,d)2. After manually creating 2 chronological rows drag down to complete sequence for that year, repeat for next year. When creating the formula, instead of using digits, reference the cells that you created ex. (A1, A2, A3) and (A4, A5, A6). Drag until you the last values you are referencing. I hope that is clear. Link to spreadsheet with my solution is here: docs.google.com/spreadsheets/d/…
    – Anthony
    Jul 28 at 19:44

1 Answer 1

0

Choose Insert > Sheet and put this formula in cell C6 of the new sheet:

=arrayformula( query( 
  { 
    eomonth('Fieldwork Log'!G6:G, -1) + 1, 
    value( { 'Fieldwork Log'!J6:J, 'Fieldwork Log'!M6:M, 'Fieldwork Log'!P6:P } ) 
  }, 
  "select Col1, sum(Col2), sum(Col3), sum(Col4) 
   where Col1 is not null 
   group by Col1 
   label sum(Col2) '', sum(Col3) '', sum(Col4) '' ", 0 
) )

This array formula will list all the months where data exists and calculate the totals without the need to drag down. To get "Total Experience Hours", put this formula in cell G6:

=arrayformula( iferror( 1 / sumif(if(column(D6:F), row(D6:F)), row(D6:F), D6:F) ^ -1 ) )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy