+ Reply to Thread
Results 1 to 2 of 2

Help for SumProduct and Counta in arrays

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Office 2016

    Question Help for SumProduct and Counta in arrays

    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.


    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)

    Last edited by madisi; 07-20-2016 at 10:48 AM. Reason: Admin Advice

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16

    Re: Array SumProduct/Sumif Issue - Asking Help!

    Hi, welcome to the forum

    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Need a SUMPRODUCT/SUMIF/INDEX/MATCH array formula. Please Help!!
    By TheClaw2323 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2015, 01:34 PM
  2. Replies: 10
    Last Post: 06-24-2014, 09:36 AM
  3. SUMPRODUCT or SUMIF using an array for criteria
    By rarascon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-24-2013, 03:14 PM
  4. sumproduct of a number array and a text array starting with 2 numbers
    By Bishonen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-21-2013, 11:48 AM
  5. Array/Sumproduct issue. Trying to "count" text.
    By purepower in forum Excel General
    Replies: 2
    Last Post: 10-24-2012, 01:44 PM
  6. Replies: 0
    Last Post: 08-17-2012, 02:02 PM
  7. Sumproduct, Sumif, or Array Formula?
    By ExcelQuestion in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-24-2007, 07:16 PM


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