Alan Moseley IT Consultancy

Blog Entry

7/25/2008 - How to SUM a range of cells based on multiple criteria

Let's say we have an Excel sheet containing the following:Column A - Year numbersColumn B - Month numbersColumn C - Sales figureHow do you get the total sales for a specific year (say 2008) and month (say 4) into a cell? You cannot use vlookup as you need to use two criteria. You need to use a SUM combined with multiple IFs in an array formula. The format is:=SUM(IF(($A$2,$A$500=2008)*($B$2:$B$500=4),$C$500))When you have typed the formula in you need to hold down Ctrl and Shift before hitting enter.