Search code examples
excelsumifs

Sum Cells and Perform Lookup for Capacity in Excel

Working with scheduling quite a lot lately and one of the manual processes that pains me greatly is figuring out what items of work can be done based on sizing efforts. For example, Person A has 49 weeks of capacity per year and I need to figure out how many items on his task list can be achieved during this time period. Sounds simple enough ...

Goal:

Auto calculate all the Estimate weeks (Column C) and perform a lookup based on capacity (Column F). If the Task estimates (plus previous tasks in this column sum is below or equal to the capacity (Column F), then mark as "Achievable" (Column D) Yes/No.

Where I'm stuck:

Can someone please shed some light on the following questions: Here is the formula I've been trying to so far: =SUMIF($C$2:C2,"<" & $G$2)

  1. It's ignoring the capacity lookup part that should be only displaying IF it is lower I believe.
  2. How do add in a "Yes" or "No" if it falls within this range to the cell that is doing the formula?

Excel Screenshot

Solution

  • It looks like your current formula is calculating correctly, but beyond the capacity in G2. If this isn't a problem, you can just use this formula in column D to show Yes/No for if the task is achievable:

    =IF(E2<=$G$2,"Yes","No")

    If you need column E to not display the numbers once they are above the capacity in G2, you could switch your formulas in column E using:

    =IF(SUM($C$2:C2)<=$G$2,SUM($C$2:C2),"")

    Alternatively, if you don't need to see the numbers in column E and only need to see if task is achievable or not, you could delete column E and use the below formula in column D:

    =IF(SUM($C$2:C2)<=$F$2,"Yes","No")