G
Guest
I am trying to convert a list of payments in this format:
Date | Account Code | Amount
into a quarterly cash flow categorised by Account Code type. I’m trying to
use VLOOKUP to convert the Account code into a category so that I can split
the cash flow into 3 categories. I have a lookup table which relates each
Account Code to one of three categories.
I’m using:
=SUMPRODUCT((Interest!$A$6:$A$256>=E$4)*(Interest!$A$6:$A$256<=E$5)*(VLOOKUP(VALUE(Interest!$C$6:$C$256),Analysis!$A$5:$G$75,7)=$A$8),(Interest!$F$6:$F$256))
The list of payments is on a sheet called interest. A6:A256 contains the
dates, E4 & E5 contain the start & end dates for each quarter, C6:C256
contains the account codes (irritatingly held as text), Analysis!A5:G75
contains the lookup table, A8 contains the category I want to look up, and
F6:F256 contains the amounts.
This formula successfully sums between the dates but doesn’t distinguish the
categories. What am I doing wrong?!
Date | Account Code | Amount
into a quarterly cash flow categorised by Account Code type. I’m trying to
use VLOOKUP to convert the Account code into a category so that I can split
the cash flow into 3 categories. I have a lookup table which relates each
Account Code to one of three categories.
I’m using:
=SUMPRODUCT((Interest!$A$6:$A$256>=E$4)*(Interest!$A$6:$A$256<=E$5)*(VLOOKUP(VALUE(Interest!$C$6:$C$256),Analysis!$A$5:$G$75,7)=$A$8),(Interest!$F$6:$F$256))
The list of payments is on a sheet called interest. A6:A256 contains the
dates, E4 & E5 contain the start & end dates for each quarter, C6:C256
contains the account codes (irritatingly held as text), Analysis!A5:G75
contains the lookup table, A8 contains the category I want to look up, and
F6:F256 contains the amounts.
This formula successfully sums between the dates but doesn’t distinguish the
categories. What am I doing wrong?!