基于多个标准的单元格的Excel SUMIFS [英] Excel SUMIFS of cells based upon multiple criteria

问题描述

我正在尝试创建一个公式，它根据多个条件计算字段数组的总和。这涉及到在一年中运行的各种特许经营权和促销活动。

我的文档包含两张，其中包含以下信息 -

• 每日收入！A：A - date。每天的专营权有一个入场券，该特定日期的收入。


• 每日收入！B：B - 特许经营名称


• 每日收入！D：D - 号码。当天的日常收入。


• '促销'！我：我 - 约会。促销的开始日期


• '促销'！J：J - 日期。促销结束日期


• '促销'！K：K - 特许经营名称

以下是示例工作表的两个屏幕截图。请注意，为了保持示例中的列，我已经删除了一些信息。

每日收入

促销

在促销标签中的列O中，我们希望计算从列I和J中的持续时间的总收入（每日收入）。

我尝试过各种SUMIFS公式，但到目前为止还没有得到任何结果。任何人都可以帮助你解决这个问题吗？

解决方案

在中输入以下公式Cell O2

  = SUMIFS（每日收入$ D $ 2：$ D $ 13，每日收入$ B $ 2 ：$ B $ 13，=& K2，每日收入$ A $ 2：$ A $ 13>& I2，每日收入$ A $ 2：$ A $ 13 & J2）
  

，并根据需要拖放/复制。根据您的数据更改公式中的范围。

以上公式将为您提供收入 c>特许经营 c $ c> 开始日期和结束日期不包括这些日期。例如，在 01/01/16 和 07/01/16 之间将为 02/01/16至06/01/16 ie 02,03,04,05,06 。如果要包含开始和结束日期，请使用以下公式：

  = SUMIFS（'Daily Revenues'！$ D $ 2： $ D $ 13，每日收入$ B $ 2：$ B $ 13，=& K2，每日收入$ A $ 2：$ A $ 13> =& I2，每日收入 ！$ A $ 2：$ A $ 13=& J2）
  

I'm trying to create a formula which calculates the sum an array of fields based upon multiple criteria. This relates to various franchises and the promotions which they run during the year

My document contains two sheets with the following information within in it -

• 'Daily Revenues'!A:A - date. There is one entry per franchise per day with the revenue for that specific day.
• 'Daily Revenues'!B:B - the franchise name
• 'Daily Revenues'!D:D - number. The daily revenue for that day.
• 'Promotions'!I:I - date. The start date of the promotion
• 'Promotions'!J:J - date. The end date of the promotion
• 'Promotions'!K:K - the franchise name

In short, we want to calculate the total revenue during each individual promotion period ('Daily Revenues'!I:I to Daily Revenues'!J:J) which each franchise has completed. We would want the total revenue to be on the same row as the details of the promotion (in 'Promotions'!O1, for example) As we have records of each promotion which each store has completed so far this year, we have 1,478 instances which makes manual calculation out of the question.

Here are two screenshots of sample worksheets. Note that some of the information I have xxx'ed out in order to maintain the columns in the example.

Daily Revenues

Promotions

In Column O in the Promotions tab, we would want to calculate the total revenues (from Daily Revenues shet) for the days from the duration in columns I and J.

I've tried various SUMIFS formulas, but haven't been able to get any results so far. Can anyone help figure this out?

解决方案

Enter the following formula in Cell O2

=SUMIFS('Daily Revenues'!$D$2:$D$13,'Daily Revenues'!$B$2:$B$13,"="&K2,'Daily Revenues'!$A$2:$A$13,">"&I2,'Daily Revenues'!$A$2:$A$13,"<"&J2)

and drag/copy down as required. Change the range in formula as per your data.

Above formula will give you Revenue for aFranchise between the Start Date and End Date excluding these dates. For example, between 01/01/16 and 07/01/16 will give result for 02/01/16 to 06/01/16i.e 02,03,04,05,06. If you want to include start and end date use the following formula:

=SUMIFS('Daily Revenues'!$D$2:$D$13,'Daily Revenues'!$B$2:$B$13,"="&K2,'Daily Revenues'!$A$2:$A$13,">="&I2,'Daily Revenues'!$A$2:$A$13,"<="&J2)

这篇关于基于多个标准的单元格的Excel SUMIFS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！