excel adding rows and columns simultaneously

[raji96]

I have two spreadsheets, i have to conditionally sum up based on the value from the other spreadsheet.

spreadsheet 1

A 10 10 100 100 100
B 20 10 10 20 30
A 3 2 10 30 30
C 40 30 40 10 30
G 20 20 20 20 20
F 40 13 39 20 18
B 90 20 20 20 20
G 19 10 20 10 30

spreadsheet 2

A 395
B 260
C 150
D 0
E 0

I have the fill the spreadsheet2 with the values of A,B,C etc but the sum should be horizontal as well as vertical.Eg.First row is A so it has to add(10+10+100+100+100) and the third row is also A so it should add(3+2+10+30+30)+(the total of A's value from the first row and put it in the spreadsheet3 similarly it should do for all the values, i tried sumif, conditionalif but couldn't figure out a simple formula.

I ended up adding the cells individually and then summing it up,but the formula turned out to be very big since i have more 23 columns.

Thanx for your help in advance.

 

[Nareik]

It can be solved with an array formula.
Remember that an array formula needs to be entered with ctrl-shift-enter.

To solve it, create a list of all the values A, B, C, etc expected.For simplicty sake put it on a new sheet, int he same workbook starting at cell a2
Then next to the list array enter the following formula
=SUM((a2=$A$1:$A$1600)*$B$1:$F$1600) and then copy down.

If you array enter correctly, excel will add 'curly braces' to the formula ie {formula in here}.

It should give you the answer. It is oly limited to 1600 rows, increase or decrease as needed.
Note that array formulas can slow down a workbook considerably.

 

[Bowers74]

Hi raji96,

The SUMPRODUCT() function does the same as an array formula, but calculates much faster, and you don't have to remember to enter using "Ctrl+Shift+Enter".

If you enter the following formula into the cell A2 on Sheet2 and have "A" in cell A1 you will get the sum of all values for the ID "A" on Sheet1


=SUMPRODUCT((Sheet1!$A$1:$A$8=$A13)*(Sheet1!$B$1:$F$8))

Simply copy the formula down for each of your ID's (i.e. "A", "B", "C" etc . . .) and it will give you what you need.

No need to use array formulas for this, because, like Nareik said, they will slow down the calculation of your workbook considerably.


If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!