Search code examples
google-sheetssumarray-formulasgoogle-sheets-querygoogle-query-language

Using arrayformula to sum if on certain date

I want to count up the number of attendees for a certain month based on the number of attendees on a certain date but can't find the formula to not having it to drag down manually. I've found how to do it for one month and manually drag down this formula (which to my own surprise I've managed to figure out).

Can someone please help me figure out how to do this automatically with an arrayformula so there isn't a need to manually drag down.

Here is what I have a.t.m.

link to photo of sheet

Link to my google sheet: https://docs.google.com/spreadsheets/d/1lLhfe1JDQxsMOS1gjWr5whjj7MRgqN4xNqlmoJ3xK9E/edit?usp=sharing

Solution

  • use:

    =ARRAYFORMULA(QUERY({TEXT(B2:B, "mmmm yyyy"), C2:C}, 
 "select Col1,sum(Col2) 
  where Col2 is not null 
  group by Col1 
  label sum(Col2)''", 0))

    enter image description here

    UPDATE:

    =ARRAYFORMULA(IFNA(VLOOKUP(A2:A, 
 QUERY({TEXT('input 1'!B2:B, "mmmm yyyy"), 'input 1'!C2:C}, 
 "select Col1,sum(Col2) 
  where Col2 is not null 
  group by Col1 
  label sum(Col2)''", 0), 2, 0)))

    0