Summing Data Across Multiple Columns with Multiple Criteria - More Efficient Solutions?

[BayouBoy]

Hi, I am looking for a more efficient way to calculate the customer ranking highlighted yellow in the "Analysis" tab attached.

My source data is included in the adjacent tab labeled "Data."

The data source contains a list of locations by row (col A), a proximity rank (col B) according to how close the location is to company HQ (note some location rankings are ties), and the remaining columns include customer name and customer spend at each location (note customers can spend at multiple locations).

I have created a weighted average proximity rank per dollar spent in the "Analysis" tab. Right now, I am running into two problems when using this approach for a larger data set:

1. Is there a formulaic solution for creating the unique list of customer names (col A) in the "Analysis" tab? Right now, I am manually copying and removing duplicates.
2. I am aggregating the spend amounts via SUMIFS formulas, but my 32-bit excel runs out of memory when I expand this to a larger data set. In reality, I have thousands of locations and thousands of customers to analyze.

Thanks in advance for any ideas for more efficient solutions.

BB

[PaulM100]

In A and down for uniques: =INDEX(Data!$A2:$A100, MATCH(0, COUNTIF($A$2:A2,Data!A2:A100), 0)) - CSE formula(use Ctrl+Shift+Enter)
in B3 down and right: =SUMPRODUCT((Data!$A$2:$A$13=$A3)*(Data!$B$2:$B$13=B$2)*(Data!$H$2:$L$13)) don't use sumproduct for entire columns(A:D) use it on ranges(A2:D1000)

[BayouBoy]

In A and down for uniques: =INDEX(Data!$A2:$A100, MATCH(0, COUNTIF($A$2:A2,Data!A2:A100), 0)) - CSE formula(use Ctrl+Shift+Enter)
in B3 down and right: =SUMPRODUCT((Data!$A$2:$A$13=$A3)*(Data!$B$2:$B$13=B$2)*(Data!$H$2:$L$13)) don't use sumproduct for entire columns(A:D) use it on ranges(A2:D1000)

I appreciate the help, but this solution pulls the location into column A. I need to pull the unique list of customers (from C2:G13 in the "Data" tab) into the col A of the "Analysis" tab.

To be clear, I need the resulting data to appear exactly as it does in my example in the "Analysis" tab. I just need a more efficient and scalable solution. Thanks.

[PaulM100]

Got it. Then try these formulas:
For unique Customer, array formula:

Please Login or Register  to view this content.

Wrap it in a IFERROR if you wanna get rid of errors.
For total amounts:

Please Login or Register  to view this content.

[BayouBoy]

Got it. Then try these formulas:
For unique Customer, array formula:

Please Login or Register  to view this content.

Wrap it in a IFERROR if you wanna get rid of errors.
For total amounts:

Please Login or Register  to view this content.

The unique customer formula works in the example sheet. However, when I apply it to the larger data set I get a circular reference error. It says the references that caused the error cannot be listed. My formula is below (identical to yours except the data set is larger):

=INDEX(Sheet2!$C$2:$AA$2191, MIN(IF(COUNTIF($A$2:A2, Sheet2!$C$2:$AA$2191)=0, ROW(Sheet2!$C$2:$AA$2191)-MIN(ROW(Sheet2!$C$2:$AA$2191))+1)), MATCH(0, COUNTIF($A$2:A2, INDEX(Sheet2!$C$2:$AA$2191, MIN(IF(COUNTIF($A$2:A2, Sheet2!$C$2:$AA$2191)=0, ROW(Sheet2!$C$2:$AA$2191)-MIN(ROW(Sheet2!$C$2:$AA$2191))+1)), , 1)), 0), 1)

[BayouBoy]

The unique customer formula works in the example sheet. However, when I apply it to the larger data set I get a circular reference error. It says the references that caused the error cannot be listed. My formula is below (identical to yours except the data set is larger):

=INDEX(Sheet2!$C$2:$AA$2191, MIN(IF(COUNTIF($A$2:A2, Sheet2!$C$2:$AA$2191)=0, ROW(Sheet2!$C$2:$AA$2191)-MIN(ROW(Sheet2!$C$2:$AA$2191))+1)), MATCH(0, COUNTIF($A$2:A2, INDEX(Sheet2!$C$2:$AA$2191, MIN(IF(COUNTIF($A$2:A2, Sheet2!$C$2:$AA$2191)=0, ROW(Sheet2!$C$2:$AA$2191)-MIN(ROW(Sheet2!$C$2:$AA$2191))+1)), , 1)), 0), 1)

Apologies. The error was on my side.

Any tips for copying that uniques formula all the way down to the bottom? I double-clicked the bottom right corner of the first cell with the formula in the example file, but my larger data set won't let me do that. Manually dragging it down is the only thing I've found to work so far.

[BayouBoy]

The SUMPRODUCT solution is taking a long time to process. I may need to restructure my data in some way and aggregate it differently. The SUMPRODUCT formulas have been processing for 34 minutes and have only processed 100 rows of data (I need to process over 4,000 rows across 2,000 columns).

Is there another way to approach this?

[menem]

I'm try to write in VBA

It's take 16 min. for 50 rows (@1000 customer / @1000 spent)



Regards.

[Bo_Ry]

Please try Power Query

Open Power Query/Get and Transform. Click on New Query.

Open blank query in the editor, launch Advanced Editor and paste in the following code.

Please Login or Register  to view this content.