Formula for Sum Multiple Columns Based on Range From One Sum Function

[Aaron R Bauer]

I have three columns of data. Sales, Cost and Profit. When the user Sums the sales for any number of rows....I want the same SUM to populate for the Cost and Profit based on the number of Rows chosen in the Sales Column.

So if I SUM A3:A25 I want Cost to SUM B3:B25 and Profit to SUM C3:C25. The same number of Rows that were chosen in Column A.

But if I change the SUM in column A to A3:A23 or A3:A5000 I want the SUM's in Column B and C to SUM the same Rows based the SUM of column A.

I hope this makes sense. It seems very simple but I can't seem to find an answer online. Thank you for any help.

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E	F	G	H
1	Sales	Cost	Profit			Sales	Cost	Profit
2	84	74	10		10	435	263	172
3	16	9	7
4	40	30	10
5	14	10	4
6	86	42	44
7	17	15	2
8	78	45	33
9	65	27	38
10	35	11	24
11	44	36	8
12	50	40	10
13	95	75	20
14	79	58	21
15	72	66	6
16	------	------	------	------	------	------	------	------

Enter the row number of the sum range in E2.

This formula entered in F2 and copied across:

=SUM(A2:INDEX(A:A,$E2))

[Aaron R Bauer]

Is there not a way to extract the value "total number of rows being Summed" and place that in cell E? So the user does not have to count and figure out this manually?

If they sum column A - then the formula in Column E would figure out the number of rows being summed and apply this to the sums in column B and C.

I just don't want the user to have to figure out this value themselves. Too many manual required inputs makes for more and more chances for errors by the user.

[Tony Valko]

How do you determine how many rows are to be included in the sum?

[Aaron R Bauer]

These files are estimates so the number of Rows would change every single day. There will be many different sums as you go down the sheet.

Example - =sum(A3:A53) then again further down =sum(A54:A560) then again =sum(A:561:A670)

These are subtotals so there could be 10 rows of items....there could be 11.....there could be 501948357 rows. It changes all the time.

The user "subtotals" what it is they choose in column "A"....this is what is being sold. I want in Column B and C to mirror this same range but in its own column without the user having to manually sum all three columns......sum column A...then sum column B...then sum column C and so on which take too much time. I'm just trying to automate the "sum" in the respective columns for them.

[Tony Valko]

Sorry, I don't understand.

You said:

The user "subtotals" what it is they choose in column "A"

OK, so how are they "choosing"?

Are they using a mouse and selecting/highlighting the range?

[Aaron R Bauer]

Grocery List...
Column "A" are the prices or the items on your grocery list.
There are 10 Items on your list.
So in A11 you Sum the total of (A1:A10)
In Column B is how much the store paid for the same items.
in cell b11 there is a sum of the cost to the store (B1:B10)

If you sum (A1:A10) I want B to sum the exact same amount of items. You may have 10 Items today on your list....tomorrow you go back to the store and you have 13 items...or 500 items and so on.

If you figure out how much it cost you to buy your (x) number of items......I want it to figure out how much those same (x) number of items cost the store. Without having to sum "A" and then having to manually sum "B"

[Tony Valko]

I understand what you WANT to do but you still haven't explained (that I can understand) how you determine what range of cells to sum.

[Aaron R Bauer]

The user manually clicks the sum button and selects in column A what they want to sum. Each user could have different amount of rows to sum in column A. But is done manually by the user selecting the sum button and then selecting the range themselves.

[Tony Valko]

This is a real kludge!

This requires Excel 2013 or later as your profile indicates that's what you're using.

Let's assume the user enters this formula in cell E1:

=SUM(A1:A5)

Enter this formula in F1:

=SUM(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(E1),"=SUM(",""),"A","B"),")","")))

Enter this formula in G1:

=SUM(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(E1),"=SUM(",""),"A","C"),")","")))

The formula in F1 will get the sum from column B and the formula in G1 will get the sum from column C.

Here's a sample file created in Excel 2013 that demonstrates this.

[Aaron R Bauer]

Wonderful! Works like a charm!

It theory I was thinking this was a very simple task but wow....I have fought for hours on end trying to figure it out. Thank you so very much.

[Tony Valko]

Let's assume the user enters this formula in cell E1:

=SUM(A1:A5)

It doesn't matter how the formula gets there.

They can type it in or use the insert function or the autosum commands, it doesn't matter!

[Tony Valko]

You're welcome. Thanks for the feedback!