☰

唯一文本标准 + 其他标准的值总和

tag-icon • tag-icon microsoft-excel worksheet-function
唯一文本标准 + 其他标准的值总和

我正在努力构建一个公式，取一列价格，仅当另一列包含唯一字符串、在特定时间段内且具有指定类型时才返回这些值的总和。

数据包含客户名称，日期，交易类型， 和交易金额。我能够使用以下公式获取给定时间范围内符合我的标准的唯一值的数量：

=SUM(--(FREQUENCY(IF(Table13[Contract/Offer Date]<=EOMONTH(A12,0),IF(Table13[Contract/Offer Date]>=A12,IF(Table13[Type of Transaction]="Offer",IF(Table13[Client]<>"",IF(Table13[Client]=Table13[Client],MATCH(Table13[Client],Table13[Client],0)))))),ROW(Table13[Client])-ROW(C$20)+1)>0))

A12是我要总结的月份，C20是数据开始的地方。

为了A12（2016 年 10 月），我获得了33我需要的是表 13[合同/报价金额] 中 33 行的总和

我尝试了这个公式，但返回了不准确的结果：
=SUM(IF(Table13[Contract/Offer Date] <=EOMONTH(A12,0),IF(Table13[Contract/Offer Date]>=A12,IF(Table13[Type of Transaction]="Offer",IF(Table13[Client] <>"",IF(Table13[Client]=Table13[Client],MATCH(Table13[Client],Table13[Client],0))),Table13[Contract/Offer Amount]))))

客户端数据确实包含一些空单元格，因此纠正这一点很重要。

我知道上述公式是错误的，因为合同/报价金额位于公式的“若为假则为值”的位置，但这是我得到的最接近的答案。

任何帮助都将不胜感激，如果您需要更多详细信息/说明，请告诉我。完全卡住了。

在此处输入图片描述

更新：

通过返回初始总和、if、频率方程，我能够返回“几乎”正确的响应。唯一计数如下所示： =SUM(IF(FREQUENCY(IF(Table13[Contract/Offer Date]<=EOMONTH(A12,0),IF(Table13[Contract/Offer Date]>=A12,IF(Table13[Type of Transaction]="Offer",IF(Table13[Client]<>"",MATCH(Table13[Client],Table13[Client],0))))),ROW(Table13[Client])-ROW(C$21)+1),1))

通过用报价金额代替最后的“1”，我返回了几乎正确的结果。

=SUM(IF(FREQUENCY(IF(Table13[Contract/Offer Date]<=EOMONTH(A12,0),IF(Table13[Contract/Offer Date]>=A12,IF(Table13[Type of Transaction]="Offer",IF(Table13[Client]<>"",MATCH(Table13[Client],Table13[Client],0))))),ROW(Table13[Client])-ROW(C$21)+1),Table13[Contract/Offer Amount]))


不清楚的是为什么这只是部分起作用。我想象频率公式的结果构成一个由 1 和 0 组成的数组，并将其乘以报价金额。这样对吗？

答案1

原来的

因此，我要回答这个问题：

构建一个公式，该公式将采用一列价格，并且仅当另一列包含唯一字符串、在特定时间段内且具有指定类型时才返回这些值的总和

假设如下：

  • 您想要的“客户”位于A1（我用的是客户端3）
  • “时间段开始”位于B1（2016 年 11 月 20 日）
  • “时间段结束”C1（2016 年 11 月 30 日）
  • “类型”位于D1（提供）

然后，这个公式：

=SUMPRODUCT(--(Table13[Client]=A1)*--(Table13[Contract/Offer Date]>=B1)*--(Table13[Contract/Offer Date]<=C1)*--(Table13[Type of Transaction]=D1)*Table13[Contract/Offer Amount])

将返还 402,000 美元。

更新

尝试分解这个公式：

=SUM(IF(
  FREQUENCY(
    IF(Table13[Contract/Offer Date]<=EOMONTH(A12,0),
      IF(Table13[Contract/Offer Date]>=A12,
        IF(Table13[Type of Transaction]="Offer",
          IF(Table13[Client]<>"",
            MATCH(Table13[Client],Table13[Client],0)
      )))),
    ROW(Table13[Client])-ROW(C$21)+1),
  Table13[Contract/Offer Amount]
))

它将找出符合条件的所有客户的频率（在特定月份并且只提供优惠）。这里有两个问题。

  1. FREQUENCY还将返回所有未说明的客户的频率。当您尝试SUM使用“数量”时，这将为您提供额外的索引。
  2. 如果您解决了问题＃1，那么您将计算该客户的第一个“金额”（很可能超出条件日期范围）。

相关内容