SUMIFS with Multiple Criteria

grant53396 ✭✭
edited 12/09/19 in Formulas and Functions


I am trying to create a summary formula for Work-in-Progress (WIP) value on a KPI summary sheet, using data on a secondary sheet. I need the formula to do the following:

  • Only look up values that have a WIP% figure greater than 0
  • If a WIP% figure is available, divide the total value by the WIP%
  • Add all of these results together to establish a value of jobs in WIP

I have created this formula which can find out line-by-line =IF({Tippa WIP} > 0, {Tippa WIP} / {Tippa Value}, ""), but wondering if there is a "SUMIFS" formula that can do this on the whole column/sheet as well? 



  • Genevieve P.
    Genevieve P. Employee Admin


    It sounds like you are looking to add up all the results from your line-by-line formula, and then divide that by the number of values added up, to result in an overall WIP %. If this is correct, you can use a SUM and a COUNTIF formula to achieve your third bullet point in a new column.

    For example:

    =SUM([Column Name]:[Column Name]) / COUNTIF([Column Name]:[Column Name], >0)


    The "Column Name" should be the column that you have your line-by-line formula in. This will add up all the percentages in that column and divide it by the number of cells that have a percentage (or cells with a value more than 0). Please note that this will need to be in a separate column than "Column Name", otherwise the formula will reference itself in the result as well.

    If I have misunderstood what you were looking to do, it may be useful to see a few screen captures of the sheet you are referencing and the sheet returning the line-by-line formula.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!