+ Reply to Thread
Results 1 to 4 of 4

Alternative to SUMPRODUCT

  1. #1
    Registered User
    Join Date
    10-18-2017
    Location
    Canada
    MS-Off Ver
    2011
    Posts
    5

    Alternative to SUMPRODUCT

    Hello everyone,

    I currently have a large data-set (200,000+ rows) that continuously updates with new data. A simplified version of this data is:
    EVENT EVENT PERIOD ID1 ID2 ID3 ID4 ID5 ID6
    CORRECT SHOT 2 987654 986524 986584 965841 954682
    INCORRECT RELOAD 3 986524 984174 986584 965641 954612 925862

    I would like to count number of the event types for every ID listed. Therefore for the sample data above, the table will look something like this:
    ID# CORRECT SHOT INCORRECT LOAD
    987654 1 0
    986524 1 1
    986584 1 1
    965841 1 0
    954612 0 1
    The same ID will never be listed on the same event twice. Currently I have been using a SUMPRODUCT formula similar to:
    =SUMPRODUCT((QUERY[EVENT]="CORRECT SHOT")*(QUERY[[ID1]:[ID6]]=[@[ID'#]]))
    Where ID# is part of a table that has all of the ID numbers (~800 unique values). The plan was to have a table where the rows will be ID numbers and columns will be the event type (~10 unique events). Therefore, using ~8000 SUMPRODUCT formulas makes the sheet barely run.

    Eventually I would like to make the table dynamic such that you can filter the results by event period as well, such that it only counts events that occurred in period 2.

    Anyone have any alternatives?

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,662

    Re: Alternative to SUMPRODUCT

    Please attach a small Excel file (not pics or screenshots ... saves retyping data).

    Be sure to make it representative (formatting, layout, data types) of the real data.

    A file almost always presents a clearer idea than written description and pasted data alone.

    If you are unfamiliar with how to do this:

    To attach a file to your post,
    • be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.
    Dave

  3. #3
    Valued Forum Contributor
    Join Date
    12-25-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    506

    Re: Alternative to SUMPRODUCT

    hi prairie, it's unfortunate that your data not designed with normalization/optimization which can make use of Pivot Table to display various dynamic tables...

    regardless, is your ID# all 6-digits?

  4. #4
    Registered User
    Join Date
    10-18-2017
    Location
    Canada
    MS-Off Ver
    2011
    Posts
    5

    Re: Alternative to SUMPRODUCT

    Hi Alvin, I agree that it would make it easier, unfortunately optimization would also cause the data to exceed 1.5 million rows.

    Yes, all the ID# are 6 digit lengths. I have a masterlist of these references so the formula would not need to take into consideration any extra IDs created. I simply have all possible IDs, and if they don't show up in the data they will be listed as 0s.

    I have attached a spreadsheet with my current formula, if anyone would like to take a crack.
    Attached Files Attached Files

+ 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] Alternative to sumproduct
    By Questionz in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 06-05-2017, 11:34 PM
  2. [SOLVED] Alternative to SUMPRODUCT
    By jiteshmehta2k1 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-23-2015, 11:23 PM
  3. Sumproduct Alternative
    By CRIMEDOG in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-14-2014, 04:57 PM
  4. Alternative to SUMPRODUCT???
    By VTdude12 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-20-2011, 04:03 PM
  5. Alternative to SUMPRODUCT?
    By cjrhoads in forum Excel General
    Replies: 26
    Last Post: 05-14-2010, 03:32 PM
  6. Sumproduct alternative
    By DKerr in forum Excel General
    Replies: 6
    Last Post: 01-09-2009, 11:25 AM
  7. Alternative to SUMPRODUCT?
    By staeri@gmail.com in forum Excel General
    Replies: 1
    Last Post: 06-09-2006, 07:10 AM

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