# Subtotal/Consolidate/Pivot Table, Lookup ???

K

#### Karin

Hi, I have a worksheet with about 1000 rows and 43 columns.
In one column (A) are contract numbers that I can subtotal on, which is what
I want to do. The problem is that not all of the subtotals should be "sum",
some need to be a formula based on the sum of other columns.

Do you have any suggestions for how I can consolidate this sheet, get the
proper subtotal function, and be able to update it? A pivot table will not
let me use a formula, subtotaling comes close, but it won't keep the formula
if I retotal.

FYI: I'm familiar with vlookups and other formulas.

TIA!

T

#### TomPl

"some need to be a formula based on the sum of other columns" is pretty vague
making it dificult to come up with a specific solution. One thing you might
try is to use sumif or sumproduct to get your totals. I would do this on a
seperate worksheet because it is better if you do not mix formulas in your
data.

Tom

K

#### Karin

The formula, in two columns, needs to be: (2nd column is similar, diff
references)
=Reconcilliation!\$D\$2*(G1453/Reconcilliation!\$D\$3)
In two other columns, the formula is: =IF(AH409>AC409,AH409-AC409,0)
What I've done is subtotal and then copy the formulas in manually. I don't
know of a better way. There are 550 subtotals, but I just copy the formula
straight down, I don't have to put it in individually. It doesn't stick if I
recalculate.