I need to Sum multiple columns by referencing one formula

[Guest]

Copied from the Excel Functions Forum.

First of all, I know little about Excel formulas.

I have a spreadsheet report generated by a Visual FoxPro program using data imported from an Access database. The report is grouped and has group totals and grand totals. Formulas, instead of numbers, are stored in the Group and Grand Total cells. The spreadsheet user wants to be able to change the value in one of the detail cells and have that reflected in the Group Totals and Grand Totals.

The user may:
1) change the value in one cell,
2) delete an entire spreadsheet detail row,
3) delete an entire Group including that groups total row.

That said, here's what I want to do.

Let's say the Group Total rows are 5, 13, 19, 23, 30 and 45. and the Grand Total row is 49.

Cells F5, F13, F19, F23, F30 and F45 contain the values 5, 6, 7, 8, 9, 10.
Cells G5, G13, G19, G23, G30 and G45 contain the values 1, 2, 3, 4, 5, 6.
Cells J5, J13, J19, J23, J30 and J45 contain the values 2, 4, 6, 8, 10, 12.
Cells K5, K13, K19, K23, K30 and K45 contain the values3, 6, 9, 12, 15, 18.
Cells L5, L13, L19, L23, L30 and L45 contain the values 10, 20, 30, 40, 50, 60.

I want to reference a formula in a cell like X1 from row 49 of each column and have that formula sum up the correct column.

If the user changes a cell or deletes a row, that works fine.

The problem I know of is, if the user deletes an entire group including the total row, a "#REF!" takes the place of that cell's reference in each Grand Total cell formula. Is there anyway to get around this?

Actually, that problem is why I want to reference one formula from the six Grand Total cells. If the user needs to change one formula, that would be better then the user having to fix all six formulas.

Can this be done ?
How is the referencing accomplished in Excel ?
What would the formula look like in Excel ?

I just need this information in regards to the Excel application. I can take care of converting the formulas for use in the Visual FoxPro program.

The reason the Group Total sum formulas work, but the Grand Total 'sum' formula doesn't work is:

The Group Total sum formulas use the =SUM function to sum vertical ranges of cells as in:
=SUM(F6:F12)

The Grand Total formula is actually a series of Group Total cells added together as in:
=(F5+F13+F19+F23+F30+F45)

I've been thinking, Is there any way within Excel to pass the column letter as a parameter from the Grand Total cell to the Formula Cell?

If so, then I could use that value as a macro as in:
=(<mcol>5+<mcol>13+<mcol>19+<mcol>23+<mcol>30+<mcol>45)


Any help appreciated.

Thanks,
Gary Z.

 

[MSP77079]

The way I would do this would be with a hidden column that identifie
each row as being an "individual", "subtotal", or grand total.

Since you are not having a problem with deleting or changing individua
lines, you don't really need the identifiers for individual or gran
total, you only need to identify the subtotals.

Then, the grandtotal is found using the formula, "=SUMIF()".

Let's say that you make Column A the hidden column, and you've put th
word "subtotal" in column A in each row where you have a subtotal.
Then, the formula for grandtotal in column B (for example) would be
=SUMIF(A:A,"subtotal",B:B)

To get help with an Excel function, select the cell where you want th
formula to go, then click on Insert > Function.

Good luck

 

[JE McGimpsey]

Sounds to me that you might be able to use the SUBTOTAL() functions.

If you have:

F5: =SUBTOTAL(9,F2:F4)
F13: =SUBTOTAL(9,F6:F12)
F19: =SUBTOTAL(9,F14,F18)
....
F45: =SUBTOTAL(9,F31:F44)

Then using

F49: =SUBTOTAL(9,F2:F44)

will give you a Grand Total that ignores the included SUBTOTALs. A row
or group could be deleted with no change in the formulae.