Saturday, October 24, 2015


MS EXCEL 2003: USE AN ARRAY FORMULA TO SUM VALUES IN COLUMN A WHEN VALUE IN COLUMN B AND COLUMN C MATCH CRITERIA

This Excel tutorial explains how to use an array formula to sum the values in one column when a value in two other columns match a criteria in Excel 2003 and older versions (with screenshots and step-by-step instructions).
Question: In Microsoft Excel 2003/XP/2000/97, I need to create a formula that will sum all the values in Column A when the value on the same row in Column B is 150 and the value in Column C is the letter U.
Answer: This can be done in Excel with an array formula.
Let's look at an example.
Microsoft Excel
In cell A7, we've created the following array formula:
=SUM((A1:A5)*(B1:B5=150)*(C1:C5="U"))
When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:
{=SUM((A1:A5)*(B1:B5=150)*(C1:C5="U"))}

No comments:

Post a Comment