+ Reply to Thread
Results 1 to 9 of 9

SUMIFS not equal

  1. #1
    Registered User
    Join Date
    06-24-2020
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    9

    SUMIFS not equal

    Good Morning Everyone -

    Have multiple codes I need to sum, except for 2. These are 5541 and 5542. On the Expense tab, Col D, I have this formula, but it clearly does'nt work. Ex: On the Expense tab, Boston has $77.85 for fuel, which is correct, but Maintenance should be all codes that don't equal 5541 & 5542 for Boston. Should be $146.85, its showing $371.55???

    =SUMIFS(NetCost,Branch,$C2,Code,"<>5541")+SUMIFS(NetCost,Branch,$C2,Code,"<>5542")

    Thanks in advance!
    Sluggy
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    406

    Re: SUMIFS not equal

    Hi Sluggy,
    In D2 try: =SUMIFS(NetCost,Branch,Expense!$C2,Code,"<>5541",Code,"<>5542")

    Your formula in the first part only excludes 5541, which includes 5542, the second half does the reverse, then add both. You need to exclude both in the same formula.
    If your Question is answered; please mark it SOLVED. If you are happy with a member's solution, say 'Thanks' and click the 'Star' to Add Reputation.

  3. #3
    Registered User
    Join Date
    06-24-2020
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    9

    Re: SUMIFS not equal

    Hi ORoos - Seemed to work, but Chicago had the same amount for Maintenance and Fuel.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,915

    Re: SUMIFS not equal

    Please try at D2
    =SUMIFS(NetCost,Branch,C2,Code,"<>5541",Code,"<>5542",Code,"<>͸5541",Code,"<>͸5542")

    if you change Transactions B4 code to Number then just below formula will work

    =SUMIFS(NetCost,Branch,C2,Code,"<>5541",Code,"<>5542")
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    831

    Re: SUMIFS not equal

    Expense

    D2=SUMPRODUCT((Branch=Expense!C2)*(ISNA(MATCH(Code,Transactions!$B$3:$B$4,0)))*(NetCost))

    Copy down



    E2=SUMPRODUCT(SUMIFS(NetCost,Code,Transactions!$B$3:$B$4,Branch,Expense!C2))

    Copy down
    Last edited by CARACALLA; 06-25-2020 at 11:21 AM.

  6. #6
    Registered User
    Join Date
    06-24-2020
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    9

    Re: SUMIFS not equal

    That did it!! Thanks

  7. #7
    Registered User
    Join Date
    06-24-2020
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    9

    Re: SUMIFS not equal

    How do I reply with "Solved" ?

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,011

    Re: SUMIFS not equal

    to mark as solved go to the thread tools dropdown at the top of this post and it is in that list.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  9. #9
    Registered User
    Join Date
    06-24-2020
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    9

    Re: SUMIFS not equal

    Sambo Kid
    Thanks!!

+ 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] How to do less than or equal to cell value in SUMIFs?
    By anon in forum Excel General
    Replies: 4
    Last Post: 08-27-2019, 11:54 AM
  2. Sumifs Not equal to
    By NotSwank in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-23-2017, 06:06 PM
  3. Sumifs not equal to
    By Shoemate in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-14-2015, 01:50 PM
  4. Sumifs not equal to
    By Shoemate in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-13-2015, 11:13 AM
  5. [SOLVED] sumifs does not equal
    By rs1aj in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 06-17-2015, 10:22 PM
  6. SumIfs containing a greater than or equal to And less than or equal to
    By teton88 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-15-2014, 07:47 PM
  7. [SOLVED] SUMIFS greater than or equal
    By telton in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-11-2013, 04:55 AM

Tags for this Thread

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