Google-sheets – Count number of rows with a specific MONTH in the date of a specific cell and a Boolean condition in another cell

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-dates

Let's say I have a number of dates and whether they occupy full or half a day:

   DATE  |         WHAT        | HALF?
...
2021-4-1 | Something Something | false
2021-4-2 | Something Something | false
2021-4-5 | Something Something | false
2021-4-19| Something Something | true
2021-5-13| Something Something | false
2021-5-24| Something Something | false
2021-9-13| Something Something | true
...

I now want for each month the amount of half-days occupied in that list.
(e.g. for April that should be 1 in the examples above, for May that should be 0)

I.e. in pseudocode, I'm looking for something like

=COUNTIF(A8:A, `MOTH of row = MONTH(E2)` AND `half of row = true`)

where E2, in this case, is e.g. January stored as a date.

And A8:A contains the dates of the above table (i.e. B8:B contains the WHAT and C8:C contains the HALF?).

Best Answer

To get results by year and month, try the following in E1

=QUERY(ARRAYFORMULA({TEXT(A1:A,"yyyy-mmm"),B1:C}), 
           "select Col1, count(Col3) 
            where Col3=TRUE 
            group by Col1 
            label count(Col3) 'Half/month' ",1)

OR
To get results by just month, try the following in G1

=QUERY(ARRAYFORMULA({TEXT(A1:A,"mmm"),B1:C}), 
           "select Col1, count(Col3) 
            where Col3=TRUE 
            group by Col1 
            label count(Col3) 'Half/month' ",1)

enter image description here

Functions used:

Related Topic