excel formula Help

[marc8]

hi all small formula issue can anyone help got the basics can add, multiply, ect copy columns, but need a multiple column (sum) entry.

Row
a item £ sorted
b total £ sum of h-P (sorted) H-P x a = B all good

Column
1-200 some headings but overall

so I need
H1 - H200 column H x A But for the column in one cell H201.

so above I am looking to repeat a sum which is total amount of items x cost x 199 lines into cell 201.
the spreadsheet has already taken the amount across H-P total amount of units & X the item amount & put into a cell.

example
(A1 = £1.20) H1 20 I1 20 J1 0 So On, B1 = £48.00
repeating lines same rows different amounts
column H201 total would = H1 x A1 =£24.00, need to add 2-199 to H201?

any help would be appreciated

 

[Richard Burns]

{=SUM(A1:A200)*(H1:H200)}
use ctrl+shift+ enter to get the brackets

 

[marc8]

Thanks Richard will give it a go now

 

[marc8]

sort gone a bit wrong somehow

my sum =sum(E11*I11)*(I11:I225)
So Looking for the total cost first part & to repeat for second part.
if I do the first part of sum fine & if I do the second part of sum it is correct 178 items but don't need the 178 just 1st part of sum repeated to I11:I225

 

[SparkyChick]

I would go for a simpler approach. Array formulas can be a PITA.

You can hide columns, so you can create the formula =H1*A1 in say column M, copy and paste that using the formula painter (there is a little nobble appear on the bottom right corner of a highlighted cell, drag that and the formula in the cell will be copied to the area you select while dragging). Then, select column M by clicking the heading cell with M in it, right click and select Hide.

Then set H201 to the formula =SUM(M1:M200).

Yes, it may take longer than entering a single formula, but it's less likely to cause you headaches. I've always had problems with array formulas and normally just use the hidden column/row approach.

 

[marc8]

I would go for a simpler approach. Array formulas can be a PITA.

You can hide columns, so you can create the formula =H1*A1 in say column M, copy and paste that using the formula painter (there is a little nobble appear on the bottom right corner of a highlighted cell, drag that and the formula in the cell will be copied to the area you select while dragging). Then, select column M by clicking the heading cell with M in it, right click and select Hide.

Then set H201 to the formula =SUM(M1:M200).

Yes, it may take longer than entering a single formula, but it's less likely to cause you headaches. I've always had problems with array formulas and normally just use the hidden column/row approach.

Hi Sparkychick
thanks forgive my novice approach but how ?

 

[marc8]

Hi All

Managed to get what I need But slow =Sum(E11*I11,E12*I12,E13*I13,E14*I14,E15*I15,E16*I16,E17*I17,E18*I18,E19*I19,E20*I20,E21*I21,E22*I22,E22*I22,E23*I23)
but must be an easier way to I225

 

[SparkyChick]

It's slow, because a single change triggers a recalculation of that entire formula.

I've attached a sample of how I would go about it. If I didn't want column N visible, I'd hide it a bit like column O. To unhide a column, select the columns on either side, right click and select Unhide

 

[Richard Burns]

Yes I think Sparky Chick has the simplest solution.

Have each calculation for E11*I11.... in cells a separate column and sum that column at the bottom.
Although i do think I may be missing something from your explanation.

 

[marc8]

Yes I think Sparky Chick has the simplest solution.

Have each calculation for E11*I11.... in cells a separate column and sum that column at the bottom.
Although i do think I may be missing something from your explanation.

Hi All Put in The Extra Column & It Works Thanks for Help

 

[SparkyChick]

Yes I think Sparky Chick has the simplest solution.

Have each calculation for E11*I11.... in cells a separate column and sum that column at the bottom.
Although i do think I may be missing something from your explanation.

From mine?

 

[Richard Burns]

From mine?

No not your explanation, that was OK.
I was just a bit lost in which sums and products were required on which columns and rows from the OP, however it is now solved, thanks to you.

 

[marc8]

many thanks for help
Column 4 now does total numbers of units used. Column 5 does unit cost total.
so our row reads
1A unit cost, 1B total costs, 1C plot total units, 1D Plot Value of units.
1B= 1Ax1C
1D= 1Ax1C
225D=1D:224D Gives me overall for flat.
this is not something I required but had to be filled in for Tender, when sheet arrived with me it asked for 1A,1B & 1D.
only problem was it just gave me a load of figures of which I had to keep Dividing to reference a total number of units to check my costs. this way I can see total number of units allowed & the cost for each & total for the plot & overall of all plots.

I have not used excel spread sheet in over a year as have already got costing software.

thanks for help not sure if the information above made sense hope it did & Thanks.