CODEWITHSUNDEEP
excelindexingsum
Guillermo
Guillermo

Reputation: 3

Sum multiple values in Index/Match function

I am working on a distribution problem, analysing the volumes delivered to a set of stores (75 stores).

I have an Excel file as follows:

input

As you can see, each day does not contain the same stores, given that each store does not receive a delivery every day.

I want to get a new table that has the code of the store in the columns, and the information about volume and miles in the rows. Furthermore, I want to sum the values of the volumes given that they belong to the same store. In this example this would look like this:

output

As you can imagine, my spreadsheet is way bigger, having a total of 6500 rows and 800 columns. I was thinking about using the function combination of INDEX/MATCH, but I cannot see how to make it sum the multiple values for a given store in a given date.

Upvotes: 0

Views: 33490

Answers (2)

Dirk Reichel
Dirk Reichel

Reputation: 7979

While you need to extend this formula, you could use:

=SUMIF(INDEX($C:$F,MATCH($J2,$A:$A,0),),L$1,INDEX($C:$F,MATCH($J2,$A:$A,0)+MOD(ROW(),2)+1,))

if the table is build up like this:

example

From L2 you can simply drag down and to the left as needed ;)

EDIT
To also get the stores:

L1: {=MIN(IF(MOD(ROW($C$1:$F$6),3)=1,$C$1:$F$6))}

This is an array formula and must be entered without the {} but being confirmed with ctrl+shift+enter!

M1: =SUMPRODUCT(SMALL(IF(MOD(ROW($C$1:$F$6),3)=1,$C$1:$F$6),SUM((IF(MOD(ROW($C$1:$F$6),3)=1,$C$1:$F$6)<=L$1)*1)+1))

from M1 you can simply copy to the right.

And to get the dates (if non continuous or something like that)

J2: =MIN(A:A)
J3: =J2
J4: =SMALL(A:A,COUNTIF(A:A,"<="&J3)+1)
J5: =J4

then copy J4:J5 simply down :)

Upvotes: 1

Steven Martin
Steven Martin

Reputation: 3272

Dont put the stores in the columns, use VBA or similar to read the input files and normalize the data so that the output would be a table looking like

Store  -  Date      - Volume  -  Miles
101     10/06/2016     520        120
102     11/06/2016     500        100

Then you can always lookup a store and date or pivot the data later.

Upvotes: 1

Related Questions