Alternative to SUMPRODUCT

**Prairie** · 11-22-2017, 04:05 PM

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?

**FlameRetired** · 11-22-2017, 10:59 PM

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.

**alvin-chung** · 11-22-2017, 11:13 PM

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?

**Prairie** · 11-23-2017, 12:49 PM

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.