# Creating In-formula vectors for use in SumProduct

#### buellboy

##### New Member
Hello All,

I have this amount of imaginary cash in my imaginary accounts (geeky points for guessing the reason behind the names):
 A​ B​ C​ 1​ AccountRef Amount Currency 2​ ALFA145 100​ USD 3​ ALFA155 70​ USD 4​ ALFA75 30​ EUR 5​ ALFA90 150​ JPY 6​ ALFA146 50​ USD 7​ ALFA164 10​ CHF 8​ ALFA145 250​ USD 9​ ALFA75 90​ EUR 10​ ALFA164 90​ EUR

<tbody>
</tbody>

I then make a summary table by account/currency

 E F G H I J K L 1 TOTAL BALANCES ALFA145 ALFA155 ALFA75 ALFA90 ALFA146 ALFA164 TOTAL 2 USD 350​ 70​ 0​ 0​ 50​ 0​ 470​ 3 EUR 0​ 0​ 120​ 0​ 0​ 90​ 210​ 4 JPY 0​ 0​ 0​ 150​ 0​ 0​ 150​ 5 CHF 0​ 0​ 0​ 0​ 0​ 10​ 10​

<tbody>
</tbody>

The formulat in F2 is =SUMPRODUCT(\$B\$2:\$B\$10;1*(\$C\$2:\$C\$10=\$E2);1*(\$A\$2:\$A\$10=F\$1))

However, since ALFA145 and ALFA146 are related, I would like to add these two accounts together. I know in sumproduct I can add a --(ISNUMBER(MATCH(\$A\$2:\$A\$10; {"ALFA145,ALFA146"};0)).

The question is: is there a way of populating the {"ALFA145,ALFA145"} automatically. What I would really like is a way of automatically generating the vector based on the headers? I.e, I want to get to this result:

 E F G H I J 1 TOTAL BALANCES ALFA145,ALFA146 ALFA155 ALFA75 ALFA90 ALFA164 2 USD 400​ 70​ 0​ 0​ 0​ 3 EUR 0​ 0​ 120​ 0​ 90​ 4 JPY 0​ 0​ 0​ 150​ 0​ 5 CHF 0​ 0​ 0​ 0​ 10​

<tbody>
</tbody>

This way I can add accounts (say I wanto to sum ALFA90 and ALFA75 as they are related) by using "," separators and the formula will just get updated. This will also allow me to have multiple accounts easier than dedicating several cells.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sorry I realised that, due to my language settings, all ; are actually "," and all , are actually ";" ...here is the formula in F2 so that you can copy and paste it in a English standard set-up.
=SUMPRODUCT(\$B\$2:\$B\$10,1*(\$C\$2:\$C\$10=\$E2),1*(\$A\$2:\$A\$10=F\$1))

The way I should solve it is like this.

Make a table on a new tab.

Put in the table the related items

alfa145 (column A) => alfa145, alfa146 (column B)

alfa146 => alfa145, alfa146

After that you can use in sheet 1, a Vlookup in column D to find the corresponding data.

After that I should make pivot table.

names (alfa) as column
currency as row
amount as value (choose for sum)

Hi, sorry, no pivot tables - I have several use-cases that require me to build the vector...if this is possible.

Hi.

I agree that some sort of data rearrangement would definitely be the best course here.

Asking Excel to interpret comma-separated strings as equivalent to function-generated arrays is not as simple as you would seem to be suggesting. In fact, you would require a rather convoluted construction such as:

=SUMPRODUCT(\$B\$2:\$B\$10,1*(\$C\$2:\$C\$10=\$E2),1*(ISNUMBER(MATCH(\$A\$2:\$A\$10,INDEX(TRIM(MID(SUBSTITUTE(F\$1,",",REPT(" ",LEN(F\$1))),LEN(F\$1)*(ROW(INDIRECT("1:"&1+LEN(F\$1)-LEN(SUBSTITUTE(F\$1,",",""))))-1)+1,LEN(F\$1))),,),0))))

though if we need to go to these sorts of lengths then this just emphasizes the point about needing to re-think the data set-up. Doing that in a proper way would allow a much simpler SUMIFS approach to solve your problem.

Regards

Thanks XOR! I haven't really explored the route of the pivot tables. The problem is that I have to compare cash held in one bank with the cash held in my database...side by side to have an immediate glimpse of the differences. Pivot tables will just produce two tables. Furthermore, the data source table is not of constant size - in both columns and rows - accounts and positions and extra columns get added all the time. I will take a look to see if i can re-structure my sheet.

Hi

Another option, in G4:

=SUMPRODUCT((\$C\$2:\$C\$10=\$F4)*ISNUMBER(SEARCH(","&\$A\$2:\$A\$10&",",","&G\$3&",")),\$B\$2:\$B\$10)

Copy down and across

Ignore my solution. Unnecessarily complex. PGC's is the way to go.

Regards

That is sexy. Seriously this kind of stuff turns me on! I need help.

Thank you all for your help! I will implement pgc's solution but I also confirm XOR's soluton works!

I'm glad it helped. Thanks for the feedback.

Replies
3
Views
723
Replies
2
Views
458
Replies
5
Views
607
Replies
9
Views
526
Replies
13
Views
945

1,221,185
Messages
6,158,409
Members
451,490
Latest member
desktopace

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back