# Help for SumProduct and Counta in arrays

1. ## Help for SumProduct and Counta in arrays

Hello All

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

2. ## Re: Array SumProduct/Sumif Issue - Asking Help!

Hi, welcome to the forum

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

Please upload your file to the forum. Not all members are able - or willing - to access file-hosting sites

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1