SUMIFS with Multiple Criteria

grant53396

Hi,

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.

Hi,

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.

Thanks!