+ Reply to Thread
Results 1 to 5 of 5

Calculating cells that include a specific word but not another word

  1. #1
    Registered User
    Join Date
    01-04-2012
    Location
    Derby, England
    MS-Off Ver
    2010, 2013 2016 Pro
    Posts
    85

    Calculating cells that include a specific word but not another word

    Hi All

    I have an excel sheet to calculate incomings and outgoings for a charity, in money entry I add the description and my formula, does an analasis based on a search string, the below formula is what I am using

    =SUMIF(J$9:J$163,"*Raffle 0001*",F$9:F$163) Column J contains the description and column F contains the value

    So my search will find all items that contain the phrase "Raffle 0001"

    in column F I would have "Test Hampton Raffle (Raffle 0001)"

    what I would like to add is another formula that looks for "Raffle 0001" and "Postage" but the original formula has to change to not include "Postage"

    This will give me two figures for each Raffle, basically incoming and Outgoing

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Calculating cells that include a specific word but not another word

    You can use SUMIFS for multiple criteria, this version will not include cells that contain "Postage"

    =SUMIFS(F$9:F$163,J$9:J$163,"*Raffle 0001*",J$9:J$163,"<>*Postage*")

    Note with SUMIFS the sum range comes first
    Audere est facere

  3. #3
    Registered User
    Join Date
    01-04-2012
    Location
    Derby, England
    MS-Off Ver
    2010, 2013 2016 Pro
    Posts
    85

    Re: Calculating cells that include a specific word but not another word

    Thanks daddylonglegs

    For some reason the formula shows a date 20/02/1900

    Test Accounts.xlsx

    Sorry, I should have attached the file in the first place

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Calculating cells that include a specific word but not another word

    That's a formatting issue, just format D197 as currency the same as E197 and you should get $51

  5. #5
    Registered User
    Join Date
    01-04-2012
    Location
    Derby, England
    MS-Off Ver
    2010, 2013 2016 Pro
    Posts
    85

    Re: Calculating cells that include a specific word but not another word

    Hmm, interesting, I should have spotted that one

    Thanks for your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Remove a specific word line from a word document.
    By Will_2013 in forum Word Formatting & General
    Replies: 4
    Last Post: 05-26-2013, 07:01 PM
  2. Replies: 13
    Last Post: 08-04-2011, 09:41 AM
  3. Replies: 2
    Last Post: 11-24-2010, 12:12 AM
  4. [SOLVED] How do you subtotal but not include the word total in the cells?
    By Kiesha in forum Excel General
    Replies: 2
    Last Post: 04-20-2006, 06:45 PM
  5. search for a specific word and copy the word and the preceeding words until a comma
    By DHANANJAY in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-31-2005, 09:10 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1