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.

Subtotal with sumif

Discussion in 'Ask an Excel Question' started by Pugazh, Dec 6, 2017.

  1. Pugazh

    Pugazh New Member

    Messages:
    5
    In the attached sample file.
    Sum value of qty of machines required to come sheet "Costing"from cell range E5:E244 against each machine type given in sheet "Investment" in corresponding year 2019 to 2024.
    Value should get automatically change according to filters applied in sheet "Costing".

    What is the correct excel formula to be used?

    Attached Files:

  2. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    390
    Hi, @Pugazh!

    Please check file. I put the formula in years 2020 and 2021. Copy the formula to the another years. Blessings!

    Attached Files:

    Thomas Kuriakose likes this.
  3. Pugazh

    Pugazh New Member

    Messages:
    5
    Thank you. what is the formula to be used for 2019 (cell D3)?
  4. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    390
    Sorry... I did not seeing before. Make a little change in the main formula, and copy the formulae to another years. Blessings!

    Attached Files:

    Thomas Kuriakose and NARAYANK991 like this.
  5. Pugazh

    Pugazh New Member

    Messages:
    5
    Thank You. I got it for 2019 as well. Works well.
  6. Pugazh

    Pugazh New Member

    Messages:
    5
    Dear Mr.John,
    I moved the cells and columns to match it to my base file. Formula returns zero value. What went wrong in the formula? I attached here Book3.xls. Could you please help on this?

    Attached Files:

  7. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    390
    You have blank columns between column AN and EQ. See the change (Blue part) in the formula for D28:

    =MAX(,SUMPRODUCT(SUBTOTAL(109,OFFSET(Costing!$EP$21,ROW(Costing!$AN$22:$AN$262)-ROW(Costing!$AN$21),MATCH(D$27,Costing!$EQ$21:$EW$21))),--(Costing!$AN$22:$AN$262=$C28))-SUMIF($C$27:C$27,"<=2030",$C28:C28))

    Blessings!

Share This Page