Sumif Question

  • Thread starter Thread starter Johnnie
  • Start date Start date
J

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
 
=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.
 
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
 
Thanks Mickie, the plus sign did the trick.

מיכ×ל (מיקי) ×בידן said:
Didn't you mean:
=SUMPRODUCT((A2:A7="Carrie David")+(A2:A7="David David")+(A2:A7="Katy
David"),B2:B7)
Micky
Click to expand...
 
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,
Thanks, this works great as well.

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

Johnie
 
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!
 
Sumproduct doesn't accept wildcards but sumIF does

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