Excel: “SUMIF” with multiple criterias

问题:

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))

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

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 sum: Every number in the same column as

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

This seems easy enough.

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

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 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"

回答1:

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.

Example:

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).