MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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