xcelnovice101
Active Member
- Joined
- Aug 24, 2012
- Messages
- 368
I have a structured table [Trades] with column headers Trade_Date, Payment_Date, Code, Amount. I want to sum all amounts where Code = "GenCost" and Payment_Date = Payment_Date when the Trade_Date equals 8/2/23 for example and the Code = "Dividend". This might seem contradictory on the face of it but when the Code = "Dividend" the Payment_Date is always after/greater the Trade_Date. When the Code = GenCost the Trade_Date = Payment_Date. There may be multiple rows for such criteria which is why I'm trying to sum to a single figure. Ultimately I just want -4,312.56 for date 8/2/2023 in this example. I can't figure out how to write a formula that takes the step-wise conditions; I believe excel is taking my inputs as contradictory.
Here is my formula (I probably don't need the Unique function because I haven't seen any duplicates):
Would I be better off doing a SUMPRODUCT(IF(
Trade_Date | Payment_Date | Code | Amount |
8/2/2023 | 9/5/2023 | Dividend | 193,116.60 |
9/5/2023 | 9/5/2023 | GenCost | (4,312.56) |
Here is my formula (I probably don't need the Unique function because I haven't seen any duplicates):
Excel Formula:
UNIQUE(CHOOSECOLS(FILTER(Trades[[Trade_Date]:[Amount],Trades[Code]="GenCost")*(Trades[Payment_Date]=Trades[Payment_Date])*(Trades[Trade_Date]=8/2/2023)*(Trades[Code]="Dividend")*(Trades[Amount]),-1),FALSE,FALSE)
Would I be better off doing a SUMPRODUCT(IF(