excel formula if cell contains specific text then sum - CoursesEssaydatesCom

excel formula if cell contains specific text then sum

MrExcel Message Board - Powered by vBulletin
  • Register
  • Help

  • Forum
    • Board FAQ

      • Forum Rules
      • Guidelines for Forum Use
      • FAQ
    • Forum Actions

      • Mark Forums Read
    • Quick Links

      • Today’s Posts
      • User Tagging Statistics
      • Hash Tag Subscriptions
      • Thanks / Like Statistics
      • Hottest Threads / Posts
  • Search New Posts
  • Zero Reply Posts
  • Subscribed Threads
  •  
  • Excel Consulting Services
  • MrExcel Home
  • MrExcel Store
  • MrExcel Seminars
  • Advanced Search
  • Home
  • Forum
  • Question Forums
  • Excel Questions
  • SUMIF Text contains a certain word

Thanks Thanks:  0

Likes Likes:  0

Results 1 to 10 of 37

Thread: SUMIF Text contains a certain word

  • Thread Tools
    • Show Printable Version
  • Display
    • Switch to Hybrid Mode
    • Switch to Threaded Mode

  1. Feb 9th, 2012, 02:37 AM


    #1

    lchennales

    • View Profile

    • View Forum Posts

    • Private Message

    lchennales is offline


    New Member


    Join Date
    Nov 2007
    Location
    Argentina
    Posts
    23
    Post Thanks / Like
    Thanks (Given)
    0
    Thanks (Received)
    0
    Likes (Given)
    0
    Likes (Received)
    0
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SUMIF Text contains a certain word

    Hi,

    Does anyone know how to have a SUMIF apply if a cell contains a certain text?

    Example:

    Product Sales
    Pen $132
    Magic Pen $5
    Books $10

    I’d like a SUMIF so that if the product name contains “Pen” it would add the sales, result in example = $137.

    Can anyone help me please??
    Thanks a lot!


    Reply With Quote Reply With Quote


    0

    0

     


  2. Feb 9th, 2012, 02:49 AM


    #2

    VoG

    • View Profile

    • View Forum Posts

    • Private Message

    VoG is offline


    MrExcel MVP

    VoG's Avatar


    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Thanks (Given)
    0
    Thanks (Received)
    13
    Likes (Given)
    0
    Likes (Received)
    15
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: SUMIF Text contains a certain word

    Try

    =SUMIF(A2:A4,”*pen*”,B2:B4)

    HTH, Peter
    Please test any code on a copy of your workbook.


    Reply With Quote Reply With Quote


    0

    0

     


  3. Oct 17th, 2014, 04:05 PM


    #3

    homemadefrog

    • View Profile

    • View Forum Posts

    • Private Message

    homemadefrog is offline


    New Member


    Join Date
    Oct 2014
    Posts
    1
    Post Thanks / Like
    Thanks (Given)
    0
    Thanks (Received)
    0
    Likes (Given)
    0
    Likes (Received)
    0
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF Text contains a certain word

    Quote Originally Posted by VoG
    View Post
    Try

    =SUMIF(A2:A4,”*pen*”,B2:B4)

    Does anyone know how I can do this and search for more than one term? Meaning if I wanted to make a sum of office supplies I could search for something like:

    =SUMIF(A2:A4,”*pen* OR *paper* OR *ink*”,B2:B4)

    Which the above when attempted literally does not work.

    Thanks!


    Reply With Quote Reply With Quote


    0

    0

     


  4. Oct 17th, 2014, 04:41 PM


    #4

    Scott Huish

    • View Profile

    • View Forum Posts

    • Private Message

    Scott Huish is offline


    MrExcel MVP
    Moderator

    Scott Huish's Avatar


    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,427
    Post Thanks / Like
    Thanks (Given)
    0
    Thanks (Received)
    61
    Likes (Given)
    86
    Likes (Received)
    58
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF Text contains a certain word

    As long as there won’t be more than one of the terms in the text, then this should work:

    =SUM(SUMIF(A2:A4,”*pen*”,”*paper*”,”*ink*”,B2:B4))

    Office 2010/365


    Reply With Quote Reply With Quote


    0

    0

     


  5. Jan 28th, 2015, 01:02 PM


    #5

    jshahinian

    • View Profile

    • View Forum Posts

    • Private Message

    jshahinian is offline


    New Member


    Join Date
    Jan 2015
    Posts
    1
    Post Thanks / Like
    Thanks (Given)
    0
    Thanks (Received)
    0
    Likes (Given)
    0
    Likes (Received)
    0
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF Text contains a certain word

    Quote Originally Posted by VoG
    View Post
    Try

    =SUMIF(A2:A4,”*pen*”,B2:B4)

    This worked for me as well BUT what if I want *pen* to be the value from a certain cell? For instance, I created a list of values I am looking for; then, next to each of those values, I am creating this formula you provided to sum if it finds it within a range.

    It would make it so much easier if I can drop the formula down from the first cell and it continues to look at the cell to the left of it instead of the value I put in the asterisks.


    Reply With Quote Reply With Quote


    0

    0

     


  6. Apr 23rd, 2015, 11:01 AM


    #6

    IllMethod

    • View Profile

    • View Forum Posts

    • Private Message

    IllMethod is offline


    New Member


    Join Date
    Apr 2015
    Posts
    1
    Post Thanks / Like
    Thanks (Given)
    0
    Thanks (Received)
    0
    Likes (Given)
    0
    Likes (Received)
    0
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF Text contains a certain word

    Quote Originally Posted by jshahinian
    View Post
    This worked for me as well BUT what if I want *pen* to be the value from a certain cell? For instance, I created a list of values I am looking for; then, next to each of those values, I am creating this formula you provided to sum if it finds it within a range.

    It would make it so much easier if I can drop the formula down from the first cell and it continues to look at the cell to the left of it instead of the value I put in the asterisks.

    If you use & between the “*” and the cell reference it works fine.

    example:

    =SUM(SUMIF(A2:A4,”*”&B1&”*”,B2:B4))

    A ‘fun’ side effect of this is if the cell reference is blank it will pull everything and sum the whole range. (since ** = anything)


    Reply With Quote Reply With Quote


    0

    0

     


  7. May 19th, 2015, 05:34 PM


    #7

    lindz5970

    • View Profile

    • View Forum Posts

    • Private Message

    lindz5970 is offline


    New Member


    Join Date
    Dec 2014
    Posts
    13
    Post Thanks / Like
    Thanks (Given)
    0
    Thanks (Received)
    0
    Likes (Given)
    0
    Likes (Received)
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF Text contains a certain word

    I’m trying to do a similar formula so this was very helpful and worked for me. But i also need to do the opposite. How can I sumif if it does NOT contain “pen”?


    Reply With Quote Reply With Quote


    0

    0

     


  8. May 19th, 2015, 06:01 PM


    #8

    Scott Huish

    • View Profile

    • View Forum Posts

    • Private Message

    Scott Huish is offline


    MrExcel MVP
    Moderator

    Scott Huish's Avatar


    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,427
    Post Thanks / Like
    Thanks (Given)
    0
    Thanks (Received)
    61
    Likes (Given)
    86
    Likes (Received)
    58
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF Text contains a certain word

    =SUMIF(A2:A4,”<>*pen*”,B2:B4)

    Office 2010/365


    Reply With Quote Reply With Quote


    0

    0

     


  9. May 19th, 2015, 06:11 PM


    #9

    mperrah

    • View Profile

    • View Forum Posts

    • Private Message

    • Visit Homepage

    mperrah is offline


    Board Regular


    Join Date
    Mar 2005
    Posts
    53
    Post Thanks / Like
    Thanks (Given)
    0
    Thanks (Received)
    0
    Likes (Given)
    0
    Likes (Received)
    0
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF Text contains a certain word

    I named A2:B & lastrow as DataRange using:
    =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$B)-1)

    named A2:a & lastrow as Product using:
    =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)

    then B2:B & lastrow as Sales using:
    =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)

    Then in E2 made a data validation list source =Product

    then in D2 put: =SUMIF(DataRange,$E2,Sales)
    and copied it down 30 of cells ‘ whatever last row you want

    in D1 put: =sum(D2:D30) ‘ whatever the last row you want

    I drop down the dataVal in D2 and pick a product,
    the sumif adds the matches and puts a total at the top


    Reply With Quote Reply With Quote


    0

    0

     


  10. May 20th, 2015, 10:57 AM


    #10

    lindz5970

    • View Profile

    • View Forum Posts

    • Private Message

    lindz5970 is offline


    New Member


    Join Date
    Dec 2014
    Posts
    13
    Post Thanks / Like
    Thanks (Given)
    0
    Thanks (Received)
    0
    Likes (Given)
    0
    Likes (Received)
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF Text contains a certain word

    Quote Originally Posted by Scott Huish
    View Post
    =SUMIF(A2:A4,”<>*pen*”,B2:B4)

    that worked!! thanks so much!


    Reply With Quote Reply With Quote


    0

    0

     


Quick Navigation
Excel Questions
Top

  • Site Areas
  • Settings
  • Private Messages
  • Subscriptions
  • Who’s Online
  • Search Forums
  • Forums Home
  • Forums
  • Announcements
    1. Board Announcements
  • Question Forums
    1. Excel Questions
    2. Questions in Other Languages
    3. Power BI
    4. Microsoft Access
    5. General Excel Discussion & Other Questions
  • MrExcel Products
    1. MrExcel’s Products: Books, CDs, Podcasts
    2. Easy-XL Add-In
  • The Lounge
    1. Lounge v.2.0
  • ModelOff – Financial Modeling World Championships
    1. Past Questions
    2. ModelOff 2014
    3. General ModelOff Discussion
  • Other Forums
    1. About This Board
    2. Hall of Fame Winners
    3. Test Here

«
Previous Thread
|
Next Thread
»

Some videos you may like


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others
  • Submit to Twitter

    Twitter

  • Submit to Linked In

    Linked In

  • Submit to Google

    Google

  • Submit to Reddit

    Reddit

  • Submit to StumbleUpon

    StumbleUpon


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
  • BB code is On
  • Smilies are On
  • [IMG] code is On
  • [VIDEO] code is Off
  • HTML code is On

Forum Rules

 


DMCA.com
  • Contact Us
  • Ask Mr. Excel – Tips and Solutions for Excel
  • Privacy Statement
  • Terms of Service
  • Top
All times are GMT -4. The time now is 12:21 PM.

Powered by vBulletin®
Copyright © 2018 vBulletin Solutions, Inc. All rights reserved.

Resources saved on this page: MySQL 12.50%
vBulletin Optimisation provided by
vB Optimise (Pro) –
vBulletin Mods & Addons Copyright © 2018 DragonByte Technologies Ltd.
Feedback Buttons provided by
Advanced Post Thanks / Like (Pro) –
vBulletin Mods & Addons Copyright © 2018 DragonByte Technologies Ltd.
User Alert System provided by
Advanced User Tagging (Pro) –
vBulletin Mods & Addons Copyright © 2018 DragonByte Technologies Ltd.
Redirecting Of Banned Users provided by
Redirect Banned Users (Pro) –
vBulletin Mods & Addons Copyright © 2018 DragonByte Technologies Ltd.
All contents Copyright 1998-2018 by MrExcel Publishing.




Menu



CCM

  • Sign Up
  • Log In

  • High-Tech

  • Health

  • Sign Up
  • Language

    • English
    • Español
    • Deutsch
    • Français
    • Italiano
    • Português
    • Nederlands
    • Polski
    • हिंदी
    • Bahasa Indonesia
    • Русский
  • Log In

CCM

CCM / Twitter

CCM / Facebook





Ask a question
Report

Sum a range only if certain text in another [Closed]

Destro –
Aug 27, 2009 at 08:31 AM – Latest reply:  RR




– Feb 14, 2012 at 02:53 PM

Office Software


Ask a question
Report

Hello,

I need a formula to sum the values in a range of cells but only for the cells that have a specific text in a completely different range of cells.

For example,

sum the values for H8:I12

but only if the text in C8:C12 EQUALS "CRM"

I have 5 different text values in order of importance:

LFIP

FIP

ERM

CRM

CIP

Any help would be of great appreciation

See more 

  • Excel formula to sum cells with specific text

  • Excel conditional formatting if cell contains specific text
    (Solved) »
    Forum – Excel

  • If a cell contains any text then add a value [Solved]
    (Solved) »
    Forum – Excel

  • Conditional format range if one cell contains certain text
    »
    Forum – Excel

  • Excel formula to count cell colour and display sum in another
    »
    Forum – Excel

  • Excel formula to copy cell value from another sheet
    »
    How-To – Excel

3 replies

Best answer


Report

Respond to venkat1926





venkat1926


1865
Posts

Sunday June 14, 2009Registration dateContributorStatus

July 30, 2015

Last seen




Aug 27, 2009 at 09:25 PM


43
Thank you

your information is not complete

I assume the five text strings are in H8 to H12

and in I6 to I12 there are some numbers

now use this formula

=SUM(IF(H8:H12="CRM",I8:I12))

This is ana rray formula so INVOKE THIS FORMULA BY HITTING

CONTROL+SHIFT+ENTER.

Thank you, venkat1926
43

Something to say? Add comment

CCM
has helped 1731 users this month

Report





Destro77


1
Posts

Wednesday August 26, 2009Registration date

August 28, 2009

Last seen



Aug 28, 2009 at 01:09 AM

Thank you very much!

You know I was sure I tried that. Must have made a dumb error in the formula somewhere. It was 4am my time.

I know… excuses, excuses. Knew it was an easy one. Thanks again, much appreciated.

Report





RR


Feb 14, 2012 at 02:53 PM

The suggestion above didn’t work for me either. Ideally all of the items you add should be in a single column (not H and I) but if you keep things as is try the following:

=sum(vlookup("CRM",C8:i12,5, false),vlookup("CRM",C8:i12,6, false))

Your reply

Ask a question


Register now

Not a member yet?

  • Member requests are more likely to be responded to.
  • Members can monitor the statuses of their requests from their account pages.
  • A CCM membership gives you access to additional options.


Featured

  • Writing Professional Emails in English Writing Professional Emails in English
  • Sign Up for a Free Netflix Trial Sign Up for a Free Netflix Trial
  • No Sound from Headphone Jack No Sound from Headphone Jack
  • Reverse Image Search with Google Reverse Image Search with Google
  • Get the Transcript of a YouTube Video Get the Transcript of a YouTube Video