Hello All,
I have this amount of imaginary cash in my imaginary accounts (geeky points for guessing the reason behind the names):
<tbody>
</tbody>
I then make a summary table by account/currency
<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:
<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.
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.
