Excel：“SUMIF”具有多重标准 [英] Excel: "SUMIF" with multiple criterias

问题描述

我想做一个单元格，有多个标准。我发现这样做的方式是使用 sumproduct 。像这样

I want to do a sum of cells, with multiple criterias. I have found out that the way to do it is with sumproduct. Like this

=SUMPRODUCT((A1:A20="x")*(B1:B20="y")*(D1:D20))

我遇到的问题是A行由合并的单元格组成我不能改变）

The problem I am having is that the A row consists of merged cells (which I can't change)

在我的情况下，我想在2010年和2011年的每一行中，按照我的标准，给出一行中的每一个数字。

In my case I want to do a sum of every number in the given row under both 2010 and 2011 meeting my criterias.

2010年总和
与

$ b $相同的列中的每个数字第2行=普通，行3 = 2010

• Row 1 = "Felles" and Row 3 = "2010"
• and
• Row 2 = "Ordinary" and Row 3 = "2010"

这似乎很简单。

=sumproduct((A1:L1 = "Felles") * (A3:L3 = 2010) * (A5:L5)) + sumproduct((A2:L2 = "Ordinary") * (A3:L3 = 2010) * (A5:L5)) 

出现问题时，我要做同样的2011年数字。唯一的问题是Felles不在同一列，因为它与6个单元格合并在一起。

The problem arises when I am to do the same for the 2011 numbers. The only problem is that "Felles" is not in the same column, as it is merged with 6 cells covering each group.

2011总和：
与
- 第1行（2列返回）=Felles和第3行=2011
- 和
- 行2 =普通和第3行=2011

2011 sum: Every number in the same column as - Row 1 (2 columns back) = "Felles" and Row 3 = "2011" - and - Row 2 = "Ordinary" and Row 3 = "2011"

推荐答案

你看过 SUMIFS（）具有多个条件的 SUMIF（）此功能将允许您完全按照要执行的操作，请参阅 SUMIFS文档。

Have you looked at SUMIFS(), its for doing SUMIF() with multiple criteria. This function will allow you to do exactly what you want to do, see the SUMIFS documentation.

示例：

SUMIFS(A5:L5,A3:L3,"=2010",A2:L2,"=Ordinary")

如果您在合并的单元格中遇到问题，请将其合并，或尝试使用命名范围（在公式选项卡下）。

If you are having trouble with merged cells just un-merge them, or try using named ranges (under the formulas tab).

这篇关于Excel：“SUMIF”具有多重标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！