Excel SUMIF 多个条件 [英] Excel SUMIFs multiple conditions

问题描述

我是 Excel 函数的新手，想在 SUMIF 方面获得一些帮助(我假设 SUMIF 是此处使用的正确函数).

Am new to Excel functions and would like some help in SUMIFs (am assuming SUMIFs is the right function to use here).

本质上，我需要匹配 2 个数组并在 2 个条件匹配的情况下对特定列求和.

Essentially, I need to match 2 arrays and sum a particular column in case 2 conditions match.

我的原始数据看起来像这样

My raw Data looks something like this

--- NAV History --------         ---- Premium Paid History ----
A        B           C           G        H          I
Policy   Date        NAV         Policy   Date       Premium Paid
P1       31-Oct-13   280         P1       25-Oct-13  250 
P1       31-Nov-13   310         P2       25-Dec-13  10   
P1       31-Dec-13   550         P1       25-Dec-13  250  
P2       31-Dec-13   13

这个想法是根据 2 个条件计算每个保单的总支付金额 -

The idea is to compute Total Amount Paid against each policy based on 2 conditions -

1. 政策(A 应该匹配 G)应该匹配并且
2. 资产净值日期 (B) 应小于或等于保费支付日期 (H)

我用公式试了一下

=SUMIFS(I:I,A:A,"*"&G3:G12&"*")

但我与预期值 (E) 相差甚远.

but I am way off from the expected value (E).

--- NAV History -------------              ---- Premium Paid History ----
A        B           C     E               G        H          I
Policy   Date        NAV   Expected        Policy   Date       Premium Paid
P1       31-Oct-13   280   250             P1       25-Oct-13  250 
P1       31-Nov-13   310   250             P2       25-Dec-13  10   
P1       31-Dec-13   550   500             P1       25-Dec-13  250  
P2       31-Dec-13   13    10

推荐答案

试试这个:

=SUMIFS(I:I,G:G,A2,H:H,"<="&B2)

无法测试 ATM.
请试一试.

No way to test ATM.
Please give it a try.

已经测试过了.请参阅下面的 SS:

这篇关于Excel SUMIF 多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！