CODEWITHSUNDEEP
google-sheetssumarray-formulasgoogle-sheets-querygoogle-query-language
thijs braem
thijs braem

Reputation: 29

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

Upvotes: 1

Views: 73

Answers (1)

player0
player0

Reputation: 1

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

Upvotes: 1

Related Questions