Formulas are incredibly long, there has to be a way to clean them up...

Hello All,

I've included a small sample of a very large workbook I'm working on. The Taxable and Tax-Exempt tabs are essentially data dumps broken by brokerage and then into their respective tabs.

On the 2024 analysis tab is where I'm trying to do all my work. Cell G4:G15 is looking at the month in column "F", and trying to give me a gain/loss breakdown by month looking in the taxable tab and pulling the relevant data from each brokerage. So for the first Brokerage in Taxable "BBAE" the formula on the analysis tab is looking at column "F" Sell Date, and column "H" Gain loss. As you can see the formula is absurd, and as I add more brokerages to the Taxable tab it gets longer and longer. I'm worried eventually I'm going to run into major problem. It's already difficult enough to manage.

Cell J4:J15 is doing the same computations but in the Tax-Exempt tab.

Am I going about this the right way or is there an easier way?

Thank you all in advance!

Originally Posted by Glenn Kennedy

You long formulae are incomprehensible.

As a start, I changed the formulae in O and P to look like this:

=SUMPRODUCT(--(Taxable!$C$3:$EC$3="Gain/Loss")*Taxable!C4:EC4)

Please MANUALLY check the two red cell results. These differed from the values that you showed. I suspect that I'm right and you're wrong... but I'll leave that to you.

Then in BABY STEPS, you need to explain the monster formulae. Start with the one in G4.

In simple terms, what the hell is it meant to do???

You are correct, your values in Column P are capturing a broker I was missing. Thank you.

Originally Posted by Gregb11

Try this in cell G4 and copy down:

=SUM((FILTER(Taxable!$4:$10000,Taxable!$3:$3="Gain/Loss"))*(MONTH(FILTER(Taxable!$4:$10000,Taxable!$3:$3="Sell Date"))=$E4))

This give you the whole row to use for Broker expansion, and goes down 10,000 rows. You can adjust the ranges if you'd like. Say instead of the whole row, you wanted to go out to column FZ. You would use this:
=SUM((FILTER(Taxable!$A$4:$FZ$10000,Taxable!$A$3:$FZ$3="Gain/Loss"))*(MONTH(FILTER(Taxable!$A$4:$FZ$10000,Taxable!$A$3:$FZ$3="Sell Date"))=$E4))

OMG, this is incredible!! Thank you so much, this is so clean, and your values tie exactly to mine!

You're welcome and thanks for the rep.