Monday, July 23, 2007

Sum values in Column A when value in Column B and Column C match criteria

This is a tricky excel formula that I bet a lot of people have had a hard time finding... I wasn't even looking for it but I did look for it some time ago and wasn't able to find it so here it is:

Question: In Excel, 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 take a look at an example.

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"))}

Got it from: http://techonthenet.com/excel/questions/array1.php

I don't know how to formulate this question so people that look for it will be able to find so if you have any ideas just leave a comment...

No comments: