Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Sum a range if a cell contains a text string

This is a discussion on Sum a range if a cell contains a text string within the Excel Questions forums, part of the Question Forums category; Hi everyone The title explains it pretty well and expect this is a walk in the park for most of ...

  1. #1
    Board Regular
    Join Date
    Dec 2007
    Location
    Newcastle upon Tyne
    Posts
    236

    Default Sum a range if a cell contains a text string

    Hi everyone

    The title explains it pretty well and expect this is a walk in the park for most of you!

    If I have a cell (a1) containing the text "Sage, Navision, Hardware" how do I write a sumif formula that will add up a set of numbers based on cell A1 containing the text string "Navision" (for example)

    Hope that makes sense.

    Thanks as usual
    Stuart
    Never underestimate the power of stupid people in large groups.

  2. #2
    Board Regular
    Join Date
    Apr 2004
    Posts
    6,715

    Default Re: Sum a range if a cell contains a text string

    Something like

    =IF(FIND("Navision",A1)>0,SUM(B1:B10),0)
    Yesterday I felt on top of the world. Today its falling in on me.

  3. #3
    Board Regular MrKowz's Avatar
    Join Date
    Jun 2008
    Location
    St. Louis, MO
    Posts
    6,113

    Default Re: Sum a range if a cell contains a text string

    Quote Originally Posted by SteveO59L View Post
    Something like

    =IF(FIND("Navision",A1)>0,SUM(B1:B10),0)
    Hmm... don't think this would work, as it would sum every value B1:B10 given the condition is met. I originally thought a SUMPRODUCT would work, but it gives me a #Value error when i try, even when I do it as array...

    =SUMPRODUCT(--(FIND("Navision",A1:A100)>0),B1:B100) <---- this will not work, just some code to try to tweak
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes
    - Please use [CODE] [/CODE] tags when posting your VBA code. It retains spacing, so your code is easier to read, and therefore easier to debug.
    - Please back up your file before using any macros suggested!

  4. #4
    Board Regular
    Join Date
    Apr 2004
    Posts
    6,715

    Default Re: Sum a range if a cell contains a text string

    "Hmm... don't think this would work, as it would sum every value B1:B10 given the condition is met. I originally thought a SUMPRODUCT would work, but it gives me a #Value error when i try, even when I do it as array..."

    If you read the OP, thats exactly what he asked to do
    Yesterday I felt on top of the world. Today its falling in on me.

  5. #5
    Board Regular
    Join Date
    Apr 2004
    Posts
    6,715

    Default Re: Sum a range if a cell contains a text string

    ?
    Yesterday I felt on top of the world. Today its falling in on me.

  6. #6
    Board Regular
    Join Date
    Dec 2007
    Location
    Newcastle upon Tyne
    Posts
    236

    Default Re: Sum a range if a cell contains a text string

    Thanks Steve, that seems to do the trick nicely.

    Stuart
    Never underestimate the power of stupid people in large groups.

  7. #7
    Board Regular MrKowz's Avatar
    Join Date
    Jun 2008
    Location
    St. Louis, MO
    Posts
    6,113

    Default Re: Sum a range if a cell contains a text string

    Quote Originally Posted by SteveO59L View Post
    "Hmm... don't think this would work, as it would sum every value B1:B10 given the condition is met. I originally thought a SUMPRODUCT would work, but it gives me a #Value error when i try, even when I do it as array..."

    If you read the OP, thats exactly what he asked to do
    Doh... did read the OP... just didn't comprehend it fully. My apologies

    Just so used to people needing to sum numbers right next to what they need the criteria ran on.
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes
    - Please use [CODE] [/CODE] tags when posting your VBA code. It retains spacing, so your code is easier to read, and therefore easier to debug.
    - Please back up your file before using any macros suggested!

  8. #8
    Board Regular
    Join Date
    Jul 2008
    Location
    Surrey, UK
    Posts
    1,475

    Default Re: Sum a range if a cell contains a text string

    put a helper column in :-

    K1 =IF(ISNUMBER(FIND("Navision",a1,1),b1,0)

    and then sum across column K

    where a1 - your strings "Sage, Navision, Hardware" etc
    b1 - your values that need summing.

    Thanks

    kaps
    Read my Excel blog on

    http://simplyspreadsheets.wordpress.com/

    For more ways I can help you with Excel :-

    www.simplyspreadsheets.co.uk

  9. #9
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,297

    Default Re: Sum a range if a cell contains a text string

    As an Aside for MrKowz, You could tweak your formula like this..

    =SUMPRODUCT(--(ISNUMBER(FIND("Navision",A1:A100))),B1:B100)

    That sums column B for every row where column A contains "Navision"

    But as Steve pointed out, the actual criteria was if a SINGLE cell (A1) contained Navision, then sum ALL of column B. If A1 does not contain Navision, then do nothing.

    Also, the find will be case sensitive, you can change it to search to be NOT case sensitive
    Last edited by Jonmo1; Sep 10th, 2008 at 10:33 AM.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  10. #10
    Board Regular
    Join Date
    Dec 2007
    Location
    Newcastle upon Tyne
    Posts
    236

    Default Re: Sum a range if a cell contains a text string

    Understood - thanks for all your help as usual
    Never underestimate the power of stupid people in large groups.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com