Forum Discussion
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!
=SUMIF(D93:F137,H2,F93:F137)
Example in the file
Hope I was able to help you with this info.
NikolinoDE
- 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!!Hope I got it right now :))
= SUBTOTAL( 109,F5:F137)
Example in the inserted file
Thank you for your understanding and patience
NikolinoDE
I know I don't know anything (Socrates)
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.
Thank you so much!
I am receiving an error message and it is not inputting a number when I press enter. Is there something else I should try?
Thank you!
Here is the workbook with the formulas (I expanded the ranges).
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.
- This is brilliant. With this as a helper column you can create the equivalent of a SUBTOTALIFS.
