索引和匹配excel中的行并返回SUMPRODUCT

I needed some help with Excel functions as I am not really sure how to achieve this.

我需要一些Excel功能的帮助，因为我不确定如何实现这一点。

Basically, this picture shows my data structure:

基本上，这幅图显示了我的数据结构:

I am aware that I can use Index& Match excel functions together for columns but I am not sure if it is possible use Index&Match together on two row conditions.

我知道我可以对列使用Index&Match excel函数，但我不确定是否可能在两行条件下使用Index&Match。

Requirement:

要求:

I have three products, product A, product B and Product C. I want to be able to quickly calculate the actual revenue and and you can see the data structure in the image above. My top-most row is month. For each month, I have 4 columns, of which 2 need to be calculated should the condition match.

我有三个产品，产品A，产品B和产品c，我想能够快速计算出实际的收入，你可以看到上面图片中的数据结构。我最喜欢的一行是月。每个月有4列，如果条件匹配，需要计算其中2列。

This is the condition. An example, for product A, calculate the total sumproduct(multiples column actual customers x Price Actuals) for yearMonth 201501.

这是条件。例如，对于产品A，计算2015年1月的总产品(多列实际客户x价格实绩)。

So basically,first, for product A, it would need to find YearMonth 201501, and when it is found, it should find columns Actual Customers and Price Actuals and return the SUMPRODUCT of Actual Customers and Price Actuals.

基本上，首先，对于产品A，它需要找到year - month 201501，当它被找到时，它应该找到列实际客户和价格实绩，并返回实际客户和价格实绩的总和。

Is it possible to do this?

有可能做到吗?

I have searched the Internet a bit and I have not found a solution.

我搜索了一下互联网，还没有找到解决方案。

Update:I thought I could post the follow-up question in the same thread insted of Cristiano duplicates... For the accepted answer, in the excel formula, how can it be divided by the total amount of "actual customers" for that month?

更新:我想我可以把后续的问题贴在同一个帖子里。对于已接受的答案，在excel公式中，如何除以当月“实际客户”的总数?

Simply adding "actual customers" with a slash before IT does not work.

简单地在“实际客户”之前加上一个斜杠是行不通的。

1 个解决方案

#1

You need a cell where you specify the month you want to look up. Give that cell the name "TheMonth".

您需要一个单元格来指定要查找的月份。给这个单元命名为“TheMonth”。

You need a cell where you specify the product you want to look up. Give that cell the name "TheProduct".

您需要一个单元格来指定要查找的产品。给这个单元格命名为“TheProduct”。

Then, assuming your screenshot starts in cell A1, you can use

然后，假设屏幕截图从A1单元格开始，您可以使用

=SUMPRODUCT($B$3:$I$5*($B$1:$I$1=TheMonth)*(($B$2:$I$2="Actual Customers")+($B$2:$I$2="Price - Actuals"))*($A$3:$A$5=TheProduct))

Next time, please post a link to a workbook. It's not much fun typing in the text and numbers from a screenshot in order to verify a formula.

下次，请发布一个链接到工作簿。从截图中输入文本和数字，以验证一个公式并不是很有趣。

Here is the workbook with the formula in action.

这是使用公式的工作簿。