+ Reply to Thread
Results 1 to 9 of 9

Sumproduct, 2 arrays - do i need 3?

  1. #1
    Registered User
    Join Date
    07-24-2013
    Location
    usa
    MS-Off Ver
    Excel 2010
    Posts
    5

    Sumproduct, 2 arrays - do i need 3?

    Hi, I'm stuck and would very much appreciate if someone could help me think through my SUMPRODUCT ( ? )issue.

    company col1 col2 col3 col4
    apple entry compliant 31-Jan-13 01-Jan-12 entry compliant
    apple 31-Jan-13 31-Jan-13 entry compliant entry compliant
    apple entry compliant 31-Jan-13 entry compliant entry compliant
    apple 01-May-13 31-Jan-13 entry compliant entry compliant
    apple entry compliant 31-Jan-13 entry compliant entry compliant
    barnes and noble entry compliant entry compliant entry compliant 01-Jan-12
    barnes and noble 06-Jul-13 entry compliant entry compliant 01-Jan-12
    barnes and noble entry compliant entry compliant entry compliant 01-Jan-12
    cisco entry compliant entry compliant entry compliant entry compliant
    cisco entry compliant entry compliant entry compliant entry compliant


    For each company, I want there to be at least one instance of a date field in each col (col1, col2, col3, col4). So count=1 if there is at least one date in a column (more than 1 date, will still count as 1), grouped by company. The first company "apple" should have a count total of 3 since the 4th column does not have any dates at all.

    the kicker: the number of companies and columns will increase with every report (don't know by how many), so I was planning to set # of rows to 999, and account for all columns (A:IV).

    I've tried so many different iterations of this but I can't get it to work --> =SUMPRODUCT(($A$2:$A$999=$A2)*($B$2:$IV$999>"0"))

    Would I use SUMPRODUCT to do this?

    Help!
    Attached Files Attached Files
    Last edited by jinkeow; 08-02-2013 at 06:23 PM.

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Sumproduct, 2 arrays - do i need 3?

    jinkeow,

    Welcome to the forum!
    Attached is a modified version of your posted workbook.
    I first made three dynamic named named ranges.
    The first dynamic named range is called "ColumnHeaders" and is defined with this formula:
    Please Login or Register  to view this content.

    The second dynamic named range is called "listCompany" and is defined with this formula:
    Please Login or Register  to view this content.

    The third dynamic named range is called "listColumn" and is defined with this formula:
    Please Login or Register  to view this content.

    That way, as your data expands, the named ranges will automatically also expand to include the new data.

    Next, to get a unique list of the company names, I used Data -> Advanced Filter -> Copy to another location -> checked Unique and selected some column to the right of the data.
    I then cut and paste that list onto 'Sheet2'
    In 'Sheet2' cell B2 and copied down is this formula, which uses the above named ranges:
    Please Login or Register  to view this content.

    It does provide the correct results, but it will only work properly if the data on 'Sheet1' is sorted by company name.
    Does that work for you?
    Attached Files Attached Files
    Last edited by tigeravatar; 08-02-2013 at 06:59 PM. Reason: typo
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Sumproduct, 2 arrays - do i need 3?

    Try,

    =SUMPRODUCT(SIGN(COUNTIFS(A$2:A$999,"Company_Name",OFFSET(B$2:B$999,,COLUMN(B$1:IV$1)-COLUMN(B$1)),">0")))
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  4. #4
    Registered User
    Join Date
    07-24-2013
    Location
    usa
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Sumproduct, 2 arrays - do i need 3?

    tigeravatar, thanks, this definitely gets me the numbers I need. Is there a way to calculate this say, in column F? I would like to keep this in the same sheet if possible, so I can perform other calculations as well.

  5. #5
    Registered User
    Join Date
    07-24-2013
    Location
    usa
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Sumproduct, 2 arrays - do i need 3?

    Thanks Haseeb A, I pasted this formula in cells F2:F11, but every value returns a 0. What am I doing wrong?

  6. #6
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Sumproduct, 2 arrays - do i need 3?

    If you are using formula in F2:F11, use this

    =SUMPRODUCT(SIGN(COUNTIFS(A$2:A$999,A2,OFFSET(B$2:B$999,,COLUMN(B$1:INDEX($1:$1,COLUMN()-1))-COLUMN(B$1)),">0")))

  7. #7
    Registered User
    Join Date
    07-24-2013
    Location
    usa
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Sumproduct, 2 arrays - do i need 3?

    This totally worked, thank you so very much!!!! Now I won't worry about this over the weekend!

  8. #8
    Registered User
    Join Date
    07-24-2013
    Location
    usa
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Sumproduct, 2 arrays - do i need 3?

    Haseeb A, where was I to post the first formula you sent me?

  9. #9
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Sumproduct, 2 arrays - do i need 3?

    In the first formula, I assumed you already have unique list of all companies in a range. So using that formula to get single count for each companies instead of repeating in each cell ie F2:F11

    You can enter all UNIQUE companies you have in a different sheet A2:A4, then in B2 use,

    =SUMPRODUCT(SIGN(COUNTIFS(Sheet1!A$2:A$999,A2,OFFSET(Sheet1!B$2:B$999,,COLUMN(B$1:IV$1)-COLUMN(B$1)),">0")))

+ 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. SUMPRODUCT from using Criteria Arrays
    By quinceag in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-12-2013, 06:30 PM
  2. [SOLVED] SUMPRODUCT and different arrays
    By rodich in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 05-09-2013, 07:02 AM
  3. Sumproduct Arrays And / OR
    By Henry c in forum Excel General
    Replies: 6
    Last Post: 04-30-2010, 06:55 AM
  4. Sumproduct - complex sum arrays
    By Tofu in forum Excel General
    Replies: 0
    Last Post: 04-14-2009, 05:38 PM
  5. Sumproduct arrays
    By L. Howard Kittle in forum Excel General
    Replies: 4
    Last Post: 04-11-2006, 08:20 AM

Tags for this Thread

Bookmarks

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