SUMIFS with Multiple Criteria
Hi,
I am trying to create a summary formula for WorkinProgress (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 linebyline =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?
Comments

Hi,
It sounds like you are looking to add up all the results from your linebyline 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 linebyline 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 linebyline formula.
Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!