Google表格公式:如果子字符串在单元格范围内，则求和 [英] Google Sheets Formula: Sum if Substring in range of cells

问题描述

我有两列:

 颜色A颜色B2020年1月2日172020年3月11日2403.11.2020 12 

我试图基于Col A的子字符串求和Col B，如下所示:

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

这意味着:如果是第二个月(XX.02.XXXX)，则应基于该月对Col B中的值求和.如果是Col A中的第11个月(XX.11.XXXX)，则应该执行相同的操作，但对于Col A中具有11的单元格.

但是，它不起作用.显然，一个MID不能在一个以上的单元格上起作用吗?

解决方案

您需要使用

如果您将那些日期格式设置为日期，且类型为 date ，则可以使用 MONTH 而不是 MID 来获取月号:

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

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?

解决方案

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表格公式:如果子字符串在单元格范围内，则求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！