【原】万变不离其宗：有条件的隔列求和也不难！

你好，我是刘卓。欢迎来到我的公号，excel函数解析。之前和你分享过隔列求和的方法，今天来分享下有条件的隔列求和的方法。下面来看下数据源和结果。

下图是各买家分期付款买产品的应付款日期和金额，求截止今日应付款的总金额。以丙为例，截止今天2020-6-1应付款的总金额是4000+4000，共8000。因为最后1个日期2020-6-15大于今天的日期，所以不算。

如果没有截止今天的时间限制，就是个隔列求和的问题。用之前的方法轻松解决，比如用下面的2个公式。

=SUMIF(B$2:G$2,"*金额*",B3:G3)

=SUMPRODUCT(MOD(COLUMN(B:G),2)*B3:G3)

但是有了截止今天的时间限制，就多了个条件，问题就稍有点复杂了。其实只要掌握了方法，找到了规律，解这种题还是不难的。关键点就两个字“错位”。

第1种，sumifs多条件求和

在H3单元格输入下面的公式，向下填充。

=SUMIFS(C3:G3,C$2:G$2,"*金额*",B3:F3,"<="&TODAY())

要注意sumifs各区域的错位对应关系，如下图红色框所示。由于条件区域1对应的条件1是包含金额，所以先把不含金额的日期排除掉，也就是黄色的区域被排除掉。

剩下的还要看条件区域2的日期是否小于等于today()，大于today()的也被排除掉。最后对同时满足两个条件的求和就得到了结果。

公式还可以写为下面的形式：

=SUMIFS(C3:G3,B$2:F$2,"*日期*",B3:F3,"<="&TODAY())

第2种，sumproduct多条件求和

在H3单元格输入下面的公式，向下填充。

=SUMPRODUCT(MOD(COLUMN(A:E),2)*(B3:F3<=TODAY())*C3:G3)

这个公式和第1种sumifs的思路是一样的，还是错位的原理，只不过换了个函数，换了种写法。

MOD(COLUMN(A:E),2)这部分用列号除以2求余数，返回的结果为{1,0,1,0,1}。

B3:F3<=TODAY()这部分判断B3:F3的区域是否小于等于today的日期，返回的结果为{TRUE,TRUE,TRUE,TRUE,TRUE}。

MOD(COLUMN(A:E),2)*(B3:F3<=TODAY())*C3:G3这3部分相乘返回的结果为{2500,0,2600,0,2700}。最后用sumproduct求和。

第3种，offset的多维引用

在H3单元格输入下面的公式，向下填充。

=SUM((N(OFFSET(A3,,{1,3,5}))<=TODAY())*N(OFFSET(A3,,{2,4,6})))

OFFSET(A3,,{1,3,5})这部分以A3单元格为基点，分别向右偏移1,3,5列，得到了由B3，D3，F3组成的三维引用，用n函数降维得到了应付款日期的序列值数组，结果为{43724,43784,43876}。

N(OFFSET(A3,,{2,4,6}))这部分得到了应付款金额的数组，结果为{2500,2600,2700}。

N(OFFSET(A3,,{1,3,5}))<=TODAY()这部分判断应付款日期是否小于等于今天的日期，成立的返回true，不成立的返回false。结果为{TRUE,TRUE,TRUE}。

(N(OFFSET(A3,,{1,3,5}))<=TODAY())*N(OFFSET(A3,,{2,4,6}))这两部分相乘，得到的结果为{2500,2600,2700}。意思就是如果应付款日期小于等于今天的日期，就返回对应的应付款金额，否则就返回0。最后用sum求和。

链接：

https://pan.baidu.com/s/1JBI5g00i-x6EEQiN1DRXvQ

提取码：d2ar