MS Excel 2003: Use an array formula to sum values in Column AB when value in Column E and Column AB match criteria
This Excel tutorial explains how to use an array formula to sum the values in one column when the values 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 AB when the value on the same row in Column E is 2 and the value in Column AB is greater than 0.
Answer: This can be done in Excel with an array formula.
Let's look at an example.
In cell E15, we've created the following array formula that uses the SUM function:
=SUM((AB5:AB13)*(E5:E13=2)*(AB5:AB13>0))
When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:
{=SUM((AB5:AB13)*(E5:E13=2)*(AB5:AB13>0))}
Advertisements