Hello All
I'd need your help about the problem in subject with Excel.
As you can see in the attached file, I have a matrix that show the quantities produced by one workcenter “A” in different days.
My goal is to find the sum of the changes number on the machine.
https://www.dropbox.com/s/0vz8xfmggr...rray.xlsx?dl=0
For instance:
- in the day 2 there is 1 change (product 3 produced), as on the previous day 1 there are no production (all cells in day 1 are empty);
- in the day 3, there are 2 changes, as the Product 3 is continuing the production of the day 2 (so 0 changes), the product 5 is produced (so 1 change), the product 7 is produced (so other 1 change) --> total 2 changes. and so on for other days..
To do that, I have indicated 3 formulas:
1. counta Indicate the cell not empty for each days
2. sumproduct Indicate the sum of the cells that are consecutive
3. max: indicates the max between "counta" and "sumproduct".
My question is: does it exist a formula that contains all the 3 formulas above? My goal is to have in one single cell the result of the sum of the max ( in the example = 9)
Help Array.xlsx
To explain better following the picture with the number of changes I would find:
The Red shapes indicate the change done on the machine
The Blue shapes indicate there is NO change on the machine, as the product is already in production the previous day.
My goal is to calculate the sum of the "red Shape", so the number of the changes on the machine.
(the result for this example is 9)
Picture1.jpg
THANKS A LOT IN ADVANCE!
Bookmarks