Sumif Question

[Johnnie]

I have the following information:

a b
2 Carrie David 42.50
3 Chris David 63.00
4 David David 47.75
5 John David 36.00
6 Katy David 41.25
7 Mike David 40.00

In Call b9 I would like to sum b2:b7 if a2:a7 = Carrie David or David David
or Katy David.

Please help. Thanks
Johnnie

 

[Luke M]

=SUMPRODUCT(--(A2:A7="Carrie David"),--(A2:A7="David David"),--(A2:A7="Katy
David"),B2:B7)


Note, if you had a list of names you wanted to include in Column C, you
could do this instead:
=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A7,C:C,0))),B2:B7)
which would give you more flexibility for changes.

 

[Don Guillett]

=SUMPRODUCT((TRIM(A2:A7)={"carrie david","david david","katy david"})*B2:B7)

 

[Don Guillett]

You may have forgotten the +

 

[Bob Phillips]

Use

=SUM(SUMIF(A2:A7,{"Carrie David","David David","Katy David"},B2:B7))

HTH

Bob

 

[מיכ×ל (מיקי) ×בידן]

Didn't you mean:
=SUMPRODUCT((A2:A7="Carrie David")+(A2:A7="David David")+(A2:A7="Katy
David"),B2:B7)
Micky

 

[Johnnie]

Thanks Mickie, the plus sign did the trick.

Didn't you mean:
=SUMPRODUCT((A2:A7="Carrie David")+(A2:A7="David David")+(A2:A7="Katy
David"),B2:B7)
Micky

 

[Johnnie]

Luke
Thanks for the help. Your formula was returning a value of 0. Micky
suggested changing -- to + and it worked.

One more question, I have seen this -- on formulas I have received from
other spreadsheets. I looked it up in help and could not find out what this
symbolizes. Can you help on this?

Once again, thanks much.
Johnnie

 

[Don Guillett]

Guess you didn't see Bob's or mine?

 

[Teethless mama]

=SUM(SUMIF(A2:A7,{"Carrie","David","Katy"}&" David",B2:B7))

 

[Johnnie]

Don,
Thanks, this works great as well.

You guys are so helpful. Very thankful you willing to share your knowledge!

Johnie

 

[dantrudo]

Guys

One quick question on this. What if the column you are searching (in this case B) has several names and you want to use a wild card search for the name "David". Rather than = Carrie David, = David David, etc. can you help with a wild card that would search any field that includes the name David? Thanks guys!

 

[Don Guillett]

Sumproduct doesn't accept wildcards but sumIF does

=SUMIF(A2:A7,"="&"*david",B2:B7)

 

[Bob Phillips]

See http://xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation

HTH

Bob