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 numbers
Column B - Month numbers
Column C - Sales figure
How 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.