I want to do a sum of cells, with multiple criterias. I have found ou开发者_高级运维t 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"
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).
精彩评论