Excel: "SUMIF" with multiple criterias

2
votes

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"

Example

1
excelexcel-formula
The structure of the sheet is fixed? I mean: could I assume that Felles are the column from G to L? or even that Felles (also BHG) is six column large? – momobo
No, it could be 5 others before "Felles", and 24 after. It will vary from time to time. – Øyvind
The problem is, AFAIK it's impossible to know from a formula is a cell is whithin a merged cell. "Felles" is the value of the G1 cell and no more. Maybe there is some regularity to exploit? (i.e. coul d be that "Felles group" begin with Felles and go on until the next nonempty cell and Ordinary group begin with Ordinary and go on for 2 cells?). Then with Match() and Offset() you could do something. – momobo

1 Answers

4
votes

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