1

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?).

0

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:

Your Answer

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

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