CODEWITHSUNDEEP
excelexcel-formula
Øyvind
Øyvind

Reputation: 979

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

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"

Example

Upvotes: 2

Views: 3948

Answers (1)

klonq
klonq

Reputation: 3607

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

Upvotes: 5

Related Questions