Array Help?

D

Danny Boy

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

Billy Liddel

Dan

How about SumProduct?

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

not enered as array

If this helps, please click Yes


Peter
 
T

T. Valko

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

smartin

Danny said:
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"))
 
J

Jacob Skaria

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
 
D

David Biddulph

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top