Forum Discussion

kayleehansen's avatar
kayleehansen
Copper Contributor
Aug 29, 2021

Subtotal If

Hello! 

I have created a sumif formula that allows me to calculate the total hours my day's todo list will take me. For my work excel I have a list of things to do and what days I plan to complete them. For each task I then also have the estimated time in hours that that task should take. From there I have my week planned out to what I should be able to accomplish and also so I don't overbook myself. However, the sumif statement still calculates my hours even after I have completed a task and have it filtered in my "done" column. Is there a way to sumif / productsum / subtotalif the formula so that when I filter my done tasks it also subtracts that item from my total time? 

 

Thank you!

    • kayleehansen's avatar
      kayleehansen
      Copper Contributor
      Hi NikolinoDE!
      Unfortunately, that didn't subtract the filtered values from the subtotal like I would want. It did sum, but I want the formula to subtract the values I filter once they are "complete" as well.
      Thank you though!!
  • kayleehansen 

    Before doing the following, remove text values such as "x" from column F.

    In H3:

     

    =SUMPRODUCT(($D$5:$D$76=H2)*SUBTOTAL(103,OFFSET($D$5,ROW($D$5:$D$76)-ROW($D$5),0))*$F$5:$F$76)

     

    Fill to the right to I3.

  • kayleehansen 

    I found no one formula in your sample, but in general to work with filtered rows you may add helper column with formula as

    =AGGREGATE(3,5,B5)

    which returns 1 if row 5 is visible and 0 if hided. 

    Or you may use SUBTOTAL() which ignore hided/filtered cells.

    • JOstrow1315's avatar
      JOstrow1315
      Copper Contributor
      This is brilliant. With this as a helper column you can create the equivalent of a SUBTOTALIFS.

Resources