• 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...

  • 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

Pugazh

New Member
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?
 

Attachments

  • Book1.xlsx
    361 KB · Views: 9
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?
 

Attachments

  • Book3.xlsx
    565.1 KB · Views: 1
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!
 
Back
Top