2

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

klonq
  • 3,535
  • 4
  • 36
  • 58
Øyvind
  • 979
  • 4
  • 17
  • 34
  • 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 Jul 26 '11 at 12:14
  • No, it could be 5 others before "Felles", and 24 after. It will vary from time to time. – Øyvind Jul 26 '11 at 12:41
  • 1
    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 Jul 26 '11 at 15:37

1 Answers1

5

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

klonq
  • 3,535
  • 4
  • 36
  • 58