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!
 
Ad

Advertisements

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
 
Ad

Advertisements

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top