Google Sheets Formula: Sum if Substring in range of cells

Question

I have two columns:

Col A                    Col B
01.02.2020               17
03.11.2020               24
03.11.2020               12

I tried to sum Col B, based on the substring of Col A like so:

=SUMIF(A:A,MID(A:A,4,2)="02",B:B)
=SUMIF(A:A,MID(A:A,4,2)="11",B:B)

Which means: If it's the second (XX.02.XXXX) month, it should sum the values from Col B based on that. If it's the 11th month (XX.11.XXXX) in Col A, it should do the same but for cells where Col A has 11.

However, it doesn't work. Apparently, one cannot do the MID function over more than one cell?

kishkin kishkin · Accepted Answer · 2020-03-31T18:49:36

You need to use ARRAYFORMULA for that.

This one will give a column of sums for every month there is:

=ARRAYFORMULA(
  SUMIF(
    MID(A:A, 4, 2),
    UNIQUE(MID(FILTER(A:A, A:A <> ""), 4, 2)),
    B:B
  )
)

And if you have those dates formatted as date, having the type date, then you can use MONTH instead of MID to get the month number:

=ARRAYFORMULA(
  SUMIF(
    MONTH(F:F),
    UNIQUE(MONTH(FILTER(F:F, F:F <> ""))),
    G:G
  )
)

Google Sheets Formula: Sum if Substring in range of cells

1 Answers