1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumif formula mistake

Discussion in 'Ask an Excel Question' started by sachar, Oct 12, 2017 at 9:05 AM.

  1. sachar

    sachar Member

    Messages:
    245
    Dear All,

    With reference to the attached sample file, when I apply the following formula in cell “B20” that is not working to avoid the duplication of negative value in the “Total Housing”.

    upload_2017-10-12_12-1-19.png

    May I know what should I do to get the correct input?

    Attached Files:

    Thangavel likes this.
  2. Naresh Yadav

    Naresh Yadav Active Member

    Messages:
    146
    Hi,

    Pls confirm what is the actual value you want based on your current data structure

    regards
    Naresh
  3. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,586
    Hi ,

    The correct function to use in such situations is the SUBTOTAL function , which ignores nested SUBTOTAL function outputs.

    See the attached file.

    Narayan

    Attached Files:

    Thomas Kuriakose likes this.
  4. sachar

    sachar Member

    Messages:
    245
    Dear Naresh,

    Received with thanks.
  5. sachar

    sachar Member

    Messages:
    245
    Dear Narayank,

    Actually, what I want it will be cleared with the attach a sample file, is there any formula to get the sum of the only negative value of the total rows as I got the answer in cell “B20” by manual calculation.

    Thanking you.

    Attached Files:

  6. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    370
    Hi, to all!

    @sachar, this could be and option:
    =SUMIFS(B2:B61,B2:B61,"<0",A2:A61,"Total*")

    Blessings!
    Thomas Kuriakose likes this.
  7. sachar

    sachar Member

    Messages:
    245
    Dear John,

    Thanks to solve my problem.
  8. Thangavel

    Thangavel New Member

    Messages:
    22
    Hello, As per my understanding the below formula give the negative value but it double on housing expenses(Electricity bill & Household furnishings and equipment).

    =SUMIFS(B2:B19,B2:B19,"<0")

    Please clarify my doubt.
    Regards,
    Thangavel D
  9. sachar

    sachar Member

    Messages:
    245
    Dear Thangavel D,

    You are absolutely right and I think the above sumifs formula is perfect to avoid the double effect.
    Thangavel likes this.

Share This Page