Array Help?
 

I must have a brain cramp because I can't get the formula below to work. In
Column A I have items to buy, in Column B I have dollar amounts associated
with each item, and in column D I have placed an "x" next to items as I
purchase them. The formula below is in cell F2, and is designed to add all
purchases, when an "x" is placed in column D. If there is a purchase amount
in column B, without an "x" in Column D, than the formula shouldn't add the
purchase amounts. What I have below (when entered as an array isn't working).
Any help would be appreciated.

Thanks, Dan

=SUM(B2:B20)*(D2:D20="x")

Array Help?
 

Dan

How about SumProduct?

=SUMPRODUCT(--(D2:D20="x")*(B2:B20))

not enered as array

If this helps, please click Yes


Peter

"Danny Boy" wrote:

I must have a brain cramp because I can't get the formula below to work. In
Column A I have items to buy, in Column B I have dollar amounts associated
with each item, and in column D I have placed an "x" next to items as I
purchase them. The formula below is in cell F2, and is designed to add all
purchases, when an "x" is placed in column D. If there is a purchase amount
in column B, without an "x" in Column D, than the formula shouldn't add the
purchase amounts. What I have below (when entered as an array isn't working).
Any help would be appreciated.

Thanks, Dan

=SUM(B2:B20)*(D2:D20="x")

Array Help?
 

when entered as an array isn't working
=SUM(B2:B20)*(D2:D20="x")

Just a misplaced closing parenthesis.

Array entered.

=SUM(B2:B20*(D2:D20="x"))

You can do the same thing with this normally entered formula:

=SUMIF(D2:D20,"x",B2:B20)

--
Biff
Microsoft Excel MVP


"Danny Boy" wrote in message
...
I must have a brain cramp because I can't get the formula below to work. In
Column A I have items to buy, in Column B I have dollar amounts associated
with each item, and in column D I have placed an "x" next to items as I
purchase them. The formula below is in cell F2, and is designed to add all
purchases, when an "x" is placed in column D. If there is a purchase
amount
in column B, without an "x" in Column D, than the formula shouldn't add
the
purchase amounts. What I have below (when entered as an array isn't
working).
Any help would be appreciated.

Thanks, Dan

=SUM(B2:B20)*(D2:D20="x")

Array Help?
 

Danny Boy wrote:
I must have a brain cramp because I can't get the formula below to work. In
Column A I have items to buy, in Column B I have dollar amounts associated
with each item, and in column D I have placed an "x" next to items as I
purchase them. The formula below is in cell F2, and is designed to add all
purchases, when an "x" is placed in column D. If there is a purchase amount
in column B, without an "x" in Column D, than the formula shouldn't add the
purchase amounts. What I have below (when entered as an array isn't working).
Any help would be appreciated.

Thanks, Dan

=SUM(B2:B20)*(D2:D20="x")

Maybe you meant this non-array formula:

=SUMPRODUCT((B2:B20)*(D2:D20="x"))

Array Help?
 

Try SUMIF()
=SUMIF(D2:D20,"x",B2:B20)


Or try this array formula (your formula modified)
=SUM(--(B2:B20)*(D2:D20="x"))


If this post helps click Yes
---------------
Jacob Skaria


"Danny Boy" wrote:

I must have a brain cramp because I can't get the formula below to work. In
Column A I have items to buy, in Column B I have dollar amounts associated
with each item, and in column D I have placed an "x" next to items as I
purchase them. The formula below is in cell F2, and is designed to add all
purchases, when an "x" is placed in column D. If there is a purchase amount
in column B, without an "x" in Column D, than the formula shouldn't add the
purchase amounts. What I have below (when entered as an array isn't working).
Any help would be appreciated.

Thanks, Dan

=SUM(B2:B20)*(D2:D20="x")

Array Help?
 

Thanks Everyone.....Your suggestions worked!

"smartin" wrote:

Danny Boy wrote:
I must have a brain cramp because I can't get the formula below to work. In
Column A I have items to buy, in Column B I have dollar amounts associated
with each item, and in column D I have placed an "x" next to items as I
purchase them. The formula below is in cell F2, and is designed to add all
purchases, when an "x" is placed in column D. If there is a purchase amount
in column B, without an "x" in Column D, than the formula shouldn't add the
purchase amounts. What I have below (when entered as an array isn't working).
Any help would be appreciated.

Thanks, Dan

=SUM(B2:B20)*(D2:D20="x")

Maybe you meant this non-array formula:

=SUMPRODUCT((B2:B20)*(D2:D20="x"))

Array Help?
 

Or array formula =SUM((B2:B20)*(D2:D20="x")) as I don't think you need the
double unary minus.
--
David Biddulph

Jacob Skaria wrote:
Try SUMIF()
=SUMIF(D2:D20,"x",B2:B20)

Or try this array formula (your formula modified)
=SUM(--(B2:B20)*(D2:D20="x"))

If this post helps click Yes
---------------
Jacob Skaria


"Danny Boy" wrote:

I must have a brain cramp because I can't get the formula below to
work. In Column A I have items to buy, in Column B I have dollar
amounts associated with each item, and in column D I have placed an
"x" next to items as I purchase them. The formula below is in cell
F2, and is designed to add all purchases, when an "x" is placed in
column D. If there is a purchase amount in column B, without an "x"
in Column D, than the formula shouldn't add the purchase amounts.
What I have below (when entered as an array isn't working). Any help
would be appreciated.

Thanks, Dan

=SUM(B2:B20)*(D2:D20="x")