Sum if 2 Variables

Posted by Michael on May 15, 2001 2:05 PM

I have 3 columns of Data:
A B C D
1 5% XX XX
0 10% XX -
1 6% - XX

If column C has an "XX" and column A has a "1" I want to add the corresponding value under column B. In this example the results of the formula under column C would be 5%.

Posted by Mark W. on May 15, 2001 2:10 PM

{=SUM(A2:A4*(C2:C4="XX")*B2:B4)}

This is an array formula which must be entered
using the Control+Shift+Enter key combination.
The braces, {}, are not entered by you.

Posted by Dave Hawley on May 15, 2001 3:07 PM

Hi Micheal

You could use an array formula like:
=SUM(IF(A1:A50=1,IF(C1:C50="XX",B1:B50)))
All arrays must be entered by holding down your Ctl+Shift key while pushing Enter.

There are some more examples of arrays and some other important rules on my Website under "Array Formulas"

Dave

OzGrid Business Applications