+ Reply to Thread
Results 1 to 4 of 4

SUMIF criteria: anything but Apples?

  1. #1
    Forum Contributor
    Join Date
    08-29-2017
    Location
    London England
    MS-Off Ver
    2016
    Posts
    194

    SUMIF criteria: anything but Apples?

    Hi all, I'm trying to set up a formula, but I don't know how to do the SUMIF criteria for it.

    I have column A with fruit names and column B with prices, and I need to find out the difference between the sum of apple prices (all apples (incl. different types)) and the sum of all other fruits prices. Which sounds WAY more complicated if I put it in these words the formula is simply:

    SUM (ALL APPLE PRICES) - SUM (ALL NON APPLE PRICES)

    for which I am trying to use SUMIF as:

    =SUMIF([Fruits],"Apple*",[Prices])-SUMIF([Fruits],NOT("Apple*"),[Prices])

    but it doesn't seem to work.. when I use evaluate formula, I can see that NOT("Apple*") gives value error.. which I think I understand why...

    but I don't know how else to set the SUMIF criteria up to mean SUM anything and everything that is not "Apple*"

    Can I get some help with this please?

    Thank you!
    Last edited by LIL2606; 05-28-2019 at 04:05 AM.

  2. #2
    Forum Contributor
    Join Date
    08-29-2017
    Location
    London England
    MS-Off Ver
    2016
    Posts
    194

    Re: SUMIF criteria: anything but Apples?

    Never mind I found the solution

    =SUMIF([Fruits],"Apple*",[Prices])-SUMIF([Fruits],"<>Apple*",[Prices])

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,368

    Re: SUMIF criteria: anything but Apples?

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,368

    Re: SUMIF criteria: anything but Apples?

    Always more rewarding when you work it out for yourself

+ 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. Apples and Oranges Counting Mystery using Excel ?
    By mbwatersuk in forum Excel General
    Replies: 4
    Last Post: 02-03-2015, 02:56 PM
  2. [SOLVED] Shorten SUMIF + SUMIF + SUMIF each with same reference range but different criteria???
    By HeyInKy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-13-2014, 09:25 PM
  3. If Row=Apples then Move row to "apples" worksheet
    By whzkd in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-23-2011, 01:29 AM
  4. Apples and Oranges = Time and Production
    By ComcoDG in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-06-2008, 06:00 PM
  5. Apples, Bananas, Oranges -- Can Excel Spit Out the Highest?
    By AnnieHall25 in forum Excel General
    Replies: 2
    Last Post: 05-02-2007, 08:24 AM
  6. [SOLVED] Who buys the apples
    By Benj in forum Excel General
    Replies: 2
    Last Post: 07-13-2005, 07:05 AM
  7. [SOLVED] Apples or pears
    By gregork in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-19-2005, 09:06 PM

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