| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Question
You may have forgotten the + -- Don Guillett Microsoft MVP Excel SalesAid Software "Luke M" wrote in message ... =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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Question
|
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 . |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIF NOT #VALUE Question | Excel Worksheet Functions | |||
| Help Please SUMIF question | Excel Discussion (Misc queries) | |||
| SUMIF Question | Excel Worksheet Functions | |||
| SUMIF Question | Excel Worksheet Functions | |||
| sumif Question | Excel Discussion (Misc queries) | |||
![Johnnie[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
