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!
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 348 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!