# need sum of one column based on searching for two values in another

Posted by Shannon on February 05, 2002 8:25 AM

I'm sure this is simple for some of you, but...

I'm trying to get a total of column G based on finding "a" or "b" in column U.

Column G also contains subtotals.

Here's the formula I've got
=SUMPRODUCT(U2:U76="a")*(U2:U76="b")*(\$G\$2:\$G\$76)

keep getting #value! error

Posted by Mark W. on February 05, 2002 8:29 AM

Try...

=SUMPRODUCT((U2:U76="a")*(U2:U76="b")*(\$G\$2:\$G\$76))

Posted by Aladin Akyurek on February 05, 2002 8:33 AM

Try:

=SUMPRODUCT(U2:U76={"a","b")*(\$G\$2:\$G\$76)

I expect that there is no "a" or "b" in U corresponding to subtotals in G.

Posted by Mark W. on February 05, 2002 8:44 AM

Oops! You're parenthesis weren't correct and I only focused on that...

Try...

{=SUM(IF((U2:U76="a")+(U2:U76="b"),\$G\$2:\$G\$76))}

Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.

Posted by Aladin Akyurek on February 05, 2002 8:49 AM

My parens gone wrong too...

It should be:

=SUMPRODUCT((U2:U76={"a","b"})*(\$G\$2:\$G\$76))

======

Posted by Caliche on February 05, 2002 11:42 AM

Try a CSE (Control_Shift_Enter) or Array formula like this:

sum(OR((U2:u76="a");(u2:u76="b"))*(g2:g76))