1. The best way of looking at this sort to problem is to figure out how to do it in intermediate steps. Nothing stops you from creating intermediate "work" tables to put the data into a more useable form. You could create an intermediate table that is used simply to group together the data into a more useable form. You could use vlookup to populate the table based on the criteria in column C. The trick with using Excel is to realize that you do not have to create the perfect in-cell formula in one shot. I think that when an in-cell formula becomes too complex and you are unable to evaluate the intermediate results then you are looking for trouble.

You can do it in various ways.

option 1
Drop the subtotals.
Grab the data and sort it by Column C
Auto subtotals for every change in column C.

option 2
Drop the subtotals.
Create a new set of rows below your data and call the A1 to A8
Now create a formula in each field using =sumif(C1..C10,"A1",{whatever column you are trying to sum}
sumif is very clever because it looks a criteria to decide whether to sum the value sumif (criteria range, criteria, sum range)

option 3
You could write a VB script to run through the data but i think it would be an overkill.

Try these methods, if you don't come right let me know thn I'll do it for you.

2. Thanks for the feedback. Was hoping to do it in one magical formuale but have effectively just created 4 totals for each "T" and then used a If and nested Sumif. Seems to have worked.

Thanks again

G

3. ## Excel help needed

I want a simple graph over my business stats over several years.

All fine, but now I want to add another set of 12m data.
I right click on graph and SELECT DATA. That field is small and my formula is long so I just copy it, update it elsewhere, but it does not allow me to paste the updated new formula back in. Just makes a sound like when one does something not possible.

Is there maybe a limit how long a formula can be?

This works (current)
='Marc'!\$E\$1:\$P\$1,'Marc'!\$R\$1:\$AC\$1,'Marc'!\$AE\$1:\$ AP\$1,'Marc'!\$AS\$1:\$BD\$1,'Marc'!\$BG\$1:\$BR\$1,'Marc'! \$BU\$1:\$CF\$1,'Marc'!\$CI\$1:\$CT\$1,'Marc'!\$E\$4:\$P\$4,'M arc'!\$R\$4:\$AC\$4,'Marc'!\$AE\$4:\$AP\$4,'Marc'!\$AS\$4:\$B D\$4,'Marc'!\$BG\$4:\$BR\$4,'Marc'!\$BU\$4:\$CF\$4,'Marc'!\$ CI\$4:\$CT\$4

but this does not work (updated):
=Marc!\$E\$1:\$P\$1,Marc!\$R\$1:\$AC\$1,Marc!\$AE\$1:\$AP\$1,M arc!\$AS\$1:\$BD\$1,Marc!\$BG\$1:\$BR\$1,Marc!\$BU\$1:\$CF\$1, Marc!\$CI\$1:\$CT\$1,Marc!\$CW\$1:\$DH\$1,Marc!\$E\$4:\$P\$4,M arc!\$R\$4:\$AC\$4,Marc!\$AE\$4:\$AP\$4,Marc!\$AS\$4:\$BD\$4,M arc!\$BG\$4:\$BR\$4,Marc!\$BU\$4:\$CF\$4,Marc!\$CI\$4:\$CT\$4, Marc!\$CW\$4:\$DH\$4

Can anybody help?

4. I spy with my little eye....lots of missing quotes around each and every.... Marc in the second expression. Maybe that is the problem?

5. The copy and paste seems to have dropped a lot of things.
I figured it out in third attempt, I had given up twice before until I asked here.
I right click SELECT DATA and then hit ENTER. That makes the formula appear on the top section of sheet and its much easier to update it there.

6. hmmm...Excel formula editing is a pain butt.

7. The way I do it now works quite well.

Page 2 of 2 First 12