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