excel - Next criteria when sumifs returns 0 - Stack Overflow

admin管理员组

文章数量:1391969

I'm running into an issue with reliable calculating some production data.
I want to calculate the total amount of produced units per year.
I already split all data from the database to different sheet (oders2020 in this example) and added a helper column (named custom) that adds the step number to the article number.
But some articles start at step 20 or higher in the production process instead of step 10.

I tried using IF statements (Excel snapshot) but that doesn't work reliable, when the production is finished at step 20 and I'm still looking at step 30 it returns 0 instead of the produced amount.
using =SUMIFS(Orders2020[Aantal];Orders2020[Custom];$A3&" "&$C$2) just returns the amount with the step number defined in C2

I'm running excel 2016 and I'm looking for a solution for sumifs to check the next criteria only if the previous was returned 0 and stop checking criteria when it finds an order amount.
Some items start production as late as step 100 and some items skip steps.

Am I on the right track or is there a function in Excel that is better suited for this purpose?
I'd like to stay clear from manual operations or VBA since it's intended as an overview tool for Maintenance and machine condition assessment that needs to be used by people who know almost nothing about excel.
To add to the challenge I'd like to stay clear from pivot tables since I'm afraid it will make the workbook to resource heavy to run smoothly on "lower end" machines.

本文标签： excelNext criteria when sumifs returns 0Stack Overflow