+ Reply to Thread
Results 1 to 3 of 3

Sumifs Not working as planed add values if cost codes match and ignore negative values

  1. #1
    Registered User
    Join Date
    06-30-2015
    Location
    Virginia
    MS-Off Ver
    2010
    Posts
    6

    Sumifs Not working as planed add values if cost codes match and ignore negative values

    So I want to add values if the cost codes match but I would also like the calculation to ignore negative values. example: 20,000 + (10,000) = 30,000

    I have Tried this simifs( [Forecasted Remaining to Spend], [Cost Code], A53, [Forecasted Remaining to Spend], ">0") +

    (simifs( [Forecasted Remaining to Spend], [Cost Code], A53, [Forecasted Remaining to Spend], "<0")*-1)

    Both sumifs statements return zero?

    Please Help

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,417

    Re: Sumifs Not working as planed add values if cost codes match and ignore negative values

    Suggest you post a sample workbook with some typical data.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    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


  3. #3
    Registered User
    Join Date
    06-30-2015
    Location
    Virginia
    MS-Off Ver
    2010
    Posts
    6

    Re: Sumifs Not working as planed add values if cost codes match and ignore negative values

    there are two tables on one sheet top table shows the budget and associated cost code. Below is a table for our grocery list or remaining to spend I want to match the cost code above to pull down the budget but if there are multiple cost codes I want them to add together. ( that I have already figured out with a simple sumif statement ). what I want is to add multiple items to the grocery list to better track my spending which I also have working. (IFERROR(D54/(SUMIF([Cost Code],A53,[Forecasted Remaining to Spend])),1). The Problem is if I am predicting a credit of -10,000 and predicting to spend 30,000 the money spent this month is 20,000. the calculation would give me 20,000/40,000 = .5 then I want my remaining to spend to update to a -5,000 credit and 15,000 predicted to spend.

+ 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. COUNTIFS & SUMIFS Not Grabbing Negative Values
    By boynejs in forum Excel General
    Replies: 4
    Last Post: 11-06-2014, 01:07 PM
  2. [SOLVED] VLOOKUP match on first x values or ignore last x values - partial match
    By bobtu in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-17-2014, 10:00 PM
  3. Replies: 6
    Last Post: 01-16-2014, 01:30 PM
  4. Ignore all negative values when counting in another column.
    By Quagga in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-18-2011, 04:38 AM
  5. How do I ignore negative values in a formula
    By queestionable in forum Excel General
    Replies: 2
    Last Post: 08-16-2007, 10:26 AM
  6. ignore negative values when adding
    By Eqa in forum Excel General
    Replies: 3
    Last Post: 11-09-2005, 05:15 AM
  7. how do i sum up a column of time values but ignore negative values
    By Croll in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 10-11-2005, 01:05 PM

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