|
Sumif Question
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 |
Sumif Question
=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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Johnnie" wrote: 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 |
Sumif Question
=SUMPRODUCT((TRIM(A2:A7)={"carrie david","david david","katy david"})*B2:B7)
-- Don Guillett Microsoft MVP Excel SalesAid Software "Johnnie" wrote in message ... 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 |
Sumif Question
Use
=SUM(SUMIF(A2:A7,{"Carrie David","David David","Katy David"},B2:B7)) HTH Bob "Johnnie" wrote in message ... 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 |
Sumif Question
Didn't you mean:
=SUMPRODUCT((A2:A7="Carrie David")+(A2:A7="David David")+(A2:A7="Katy David"),B2:B7) Micky "Luke M" wrote: =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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Johnnie" wrote: 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 |
Sumif Question
Thanks Mickie, the plus sign did the trick.
"מיכאל (מיקי) אבידן" wrote: Didn't you mean: =SUMPRODUCT((A2:A7="Carrie David")+(A2:A7="David David")+(A2:A7="Katy David"),B2:B7) Micky "Luke M" wrote: =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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Johnnie" wrote: 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 |
Sumif Question
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 "Luke M" wrote: =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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Johnnie" wrote: 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 |
Sumif Question
|
Sumif Question
=SUM(SUMIF(A2:A7,{"Carrie","David","Katy"}&" David",B2:B7))
"Johnnie" wrote: 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 |
Sumif Question
Don,
Thanks, this works great as well. You guys are so helpful. Very thankful you willing to share your knowledge! Johnie "Don Guillett" wrote: =SUMPRODUCT((TRIM(A2:A7)={"carrie david","david david","katy david"})*B2:B7) -- Don Guillett Microsoft MVP Excel SalesAid Software "Johnnie" wrote in message ... 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 . |
Sumif Question
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! --- frmsrcurl: http://msgroups.net/microsoft.public...Sumif-Question |
Sumif Question
Sumproduct doesn't accept wildcards but sumIF does
=SUMIF(A2:A7,"="&"*david",B2:B7) -- Don Guillett Microsoft MVP Excel SalesAid Software "dantrudo" / wrote in message ... 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! --- frmsrcurl: http://msgroups.net/microsoft.public...Sumif-Question |
Sumif Question
See http://xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation HTH Bob "Johnnie" wrote in message ... 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 "Luke M" wrote: =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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Johnnie" wrote: 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 |
| All times are GMT +1. The time now is 05:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com