CODEWITHSUNDEEP
arraysregexgoogle-sheetspivot-tablegoogle-query-language
Jhw_Trw
Jhw_Trw

Reputation: 162

Sum rows that have the same month in relation to the Date parameter

I'm using Google Sheets and I have two tables:

Table 1(Records): Columns [entry date, exit date and duration] > Duration is the difference between exit date and entry date. Only data from a single year are considered.

Table 2: 12 columns (one for each month). I need to make a row in the table display the sum of the durations for each respective month.

Records:

Entry Date Exit Date Duration
01/01/2021 02/01/2021 1
04/01/2021 06/01/2021 2
11/02/2021 15/02/2021 4
21/02/2021 24/02/2021 3
05/03/2021 06/03/2021 1

Table2 must show:

Jan Feb Mar Apr ... Dec
total duration 3 7 1 0 ... 0

What expression should I use to do this? Any answers/suggestions/tips?

Upvotes: 1

Views: 55

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(REGEXREPLACE(""&QUERY({TEXT(A2:A, "mm\×mmm"), C2:C; 
 IFERROR(TEXT(SEQUENCE(12, 1, 1, 28), {"mm\×mmm", "\0"*1}), 0)}, 
 "select sum(Col2) pivot Col1"), "^\d+×", ))

enter image description here

Upvotes: 2

Related Questions