SUMIFS求和时间范围之外如何表达？

pcsoftware

请教两个问题。示例文档在附件。A列是发货金额，B列是发货日期，格式都是20200101这种。


问题1、统计2020年发货的金额，我用公式=sumifs(A:A,B:B,">=20200101",B:B,"<=20201231")，手动核验，结果正确。
有没更简单的公式？例如判断B列数据取左边4位，如果等于2020，就求和。我比较菜，想不出公式。


问题2、我想统计2020年之外的发货金额，笨办法是把2个SUMIFS相加，公式如下：

=sumifs(A:A,B:B,"<20200101")+sumifs(A:A,B:B,">20201231")
手动核验，结果正确。
但如果年份有很多，公式就会变得很长，并不好。
有没更简单的公式？

感谢大神们的解答，请帮帮忙。

A1233515

问题一：
=SUMPRODUCT((LEFT($B$2:$B$9,4)="2020")*$A$2:$A$9)

A1233515

问题二：
=SUMPRODUCT((LEFT($B$2:$B$9,4)<>"2020")*$A$2:$A$9)

pcsoftware

A1233515 发表于 2021-2-25 08:19
问题一：
=SUMPRODUCT((LEFT($B$2:$B$9,4)="2020")*$A$2:$A$9)

谢谢大神解答，LEFT能和SUMIFS嵌套吗？
试了下公式=SUMIFS(A:A,B:B,LEFT(B:B,4)="2020")，结果为0
请帮忙改改。

A1233515

SUMIF(range, criteria, [sum_range])
sumif要求的是单元格对象范围，数组对象的话，还是sumproduct方便

pcsoftware

A1233515 发表于 2021-2-25 09:43
SUMIF(range, criteria, [sum_range])
sumif要求的是单元格对象范围，数组对象的话，还是sumproduct方便

还请教个问题。
第一行是表头，是各列的名称，例如A1是发货金额，B1列是发货日期。
我知道sumproduct不能引用整列，比如A列为发货金额，因为表头是文本，非表头部分是数字，这函数用整列会引起混乱，返回结果#VALUE!，只有把A:A改为$A$2:$A$9，才能出正常的结果。
SUMIFS的范围为整列，例如A:A时，表头会好像对发货金额的统计没影响。用SUMIFS时，我用A:A好点，还是$A$2:$A$9呢？请赐教下有没隐患。不想年报表搞完了发现需要修改。

A1233515

pcsoftware 发表于 2021-2-25 19:18
还请教个问题。
第一行是表头，是各列的名称，例如A1是发货金额，B1列是发货日期。
我知道sumproduct不 ...

一般来说，整列引用和精确引用对于sumif没有什么区别
但是，如果你的数据量大的话，整列引用可能会引起卡顿，甚至死机，那这样就是精确引用好一点