ScottyDundee
New Member
- Joined
- Dec 21, 2016
- Messages
- 1
I am doing an index+match of a portfolio value using a client identifier plus date. However the client can have more than 1 account (using the same identifier) and I need to add sumif to the formula. As the date being looked at can fall on a weekend I have had to expand the formula so that the last working day figure is looked up.
Currently I am using the below formula:
=IFERROR(IFERROR(-INDEX('Client Data'!1:1048576,MATCH(Performance!$O$6,'Client Data'!$A:$A,0),MATCH(Performance!R1,'Client Data'!$1:$1,0)),-INDEX('Client Data'!1:1048576,MATCH(Performance!$O$6,'Client Data'!$A:$A,0),MATCH(Performance!R1-1,'Client Data'!$1:$1,0))),-INDEX('Client Data'!1:1048576,MATCH(Performance!$O$6,'Client Data'!$A:$A,0),MATCH(Performance!R1-2,'Client Data'!$1:$1,0)))
The index and match cells are highlighted in red below:
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
How do I add sumif to the formula so that the Portfolio Value will total them?
Currently I am using the below formula:
=IFERROR(IFERROR(-INDEX('Client Data'!1:1048576,MATCH(Performance!$O$6,'Client Data'!$A:$A,0),MATCH(Performance!R1,'Client Data'!$1:$1,0)),-INDEX('Client Data'!1:1048576,MATCH(Performance!$O$6,'Client Data'!$A:$A,0),MATCH(Performance!R1-1,'Client Data'!$1:$1,0))),-INDEX('Client Data'!1:1048576,MATCH(Performance!$O$6,'Client Data'!$A:$A,0),MATCH(Performance!R1-2,'Client Data'!$1:$1,0)))
The index and match cells are highlighted in red below:
| Semi-Annually | 01-Jun-13 | ||
| 01/12/2016 | |||
| 01/12/2016 | Portfolio Value | £62,170.56 | |
| % Change 6 Months | |||
| 100.0 | |||
| BC001232456 | Inflows/Outflows |
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
How do I add sumif to the formula so that the Portfolio Value will total them?
