none
Using Subtotal function with an IF Statement RRS feed

  • Question

  • I'd like to use this formula, but it has to calculate only for the rows that are displayed (after being filtered).

    =SUMIF($E2:$E1138,"=Cancel",M2:M1138)

    Can someone tell me how I Can do this, please? I cannot add any more new columns. to my spreadsheet. It must all be done at the bottom of the spreadsheet in this one cell.

    Thank you,


    John

    Tuesday, January 3, 2017 10:14 PM

Answers

  • This works

    =SUMPRODUCT(SUBTOTAL(109,OFFSET(M2,ROW(M2:M1138)-ROW(M2),,1)),--(F2:F1138 = "Cancel"))


    John

    • Marked as answer by vsla Tuesday, January 3, 2017 11:34 PM
    Tuesday, January 3, 2017 11:34 PM

All replies

  • This works

    =SUMPRODUCT(SUBTOTAL(109,OFFSET(M2,ROW(M2:M1138)-ROW(M2),,1)),--(F2:F1138 = "Cancel"))


    John

    • Marked as answer by vsla Tuesday, January 3, 2017 11:34 PM
    Tuesday, January 3, 2017 11:34 PM
  • Hi,

    Thanks for posting here and sharing the solution.

    If you have any new issues about Office object model, please feel free to post here.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, January 4, 2017 1:44 AM
    Moderator