trying to sum values if values corresponding are the same

[Eisaz]

[FONT=Verdana, Arial, Helvetica]hiya, i have a small project and i can''t figure it out on myself. What i need to do is to create a formula which
will find the same values in column A and will sum the corresponding to them
values from columns B:E. I have tried SUMIF but if seems not to work in more than 1 columns.
this is a sort of result i want to get:
A B C D E F RESULT:
1 A1 1 5 2 3 A1 31
2 B5 2 6 3 3 A2 19
3 C1 3 7 4 3 B1 22
4 A1 4 8 5 3 B5 37
5 B5 5 9 6 3 C1 33
6 C2 6 1 6 3 C2 16
7 A2 7 2 7 3
8 B1 8 3 8 3
9 C1 9 4 0 3

I would appreciate any ideas how to do this. Thanks and regards,[/FONT]

 

[texasalynn]

try this
=SUMPRODUCT(($K$12:$K$20=P12)*($L$12:$O$20))

 

[Eisaz]

thanks for such a quick response,
tried this one and doesn't seem to work. maybe i'm doing something wrong, but it gives me circular reference error. also, don't know if that actually makes a difference but im using excel2007.
any ideas ?

 

[texasalynn]

you would need to change the cell references to where your data is located. I just did a sample based on the example of gave. My data wasn't in cells A1 .... Sorry for the confusion

 

[Eisaz]

confusion isn't your fault - i did get that that i need to change cells ref. is it possible that it gives me the error because the cells i'm taking the values from are actually =ref from another sheet ?

 

[Peter_SSs]

confusion isn't your fault - i did get that that i need to change cells ref. is it possible that it gives me the error because the cells i'm taking the values from are actually =ref from another sheet ?

That shouldn't be a problem, nor Excel 2007. Here it is:

My formula in B1 is copied across to E1 and down to row 9. Formula in H1 copied down.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	A1	1	5	2	3		A1	31
2	B5	2	6	3	3		A2	19
3	C1	3	7	4	3		B1	22
4	A1	4	8	5	3		B5	37
5	B5	5	9	6	3		C1	33
6	C2	6	1	6	3		C2	16
7	A2	7	2	7	3
8	B1	8	3	8	3
9	C1	9	4	0	3
10
SUMPRODUCT

 

[Eisaz]

it works, thank you very much. i have one more question - to make it easier and more.. automated. Would it be possible, to have the values from column A inserted automaticaly in coumn G? meaning, if we have 2x A1 in column A, than it will insert A1 into column G with sum of all values of A1? i hope i make sense i will really appreciate your help, #thanks

 

[Peter_SSs]

Would it be possible, to have the values from column A inserted automaticaly in coumn G?

One way would be to put a heading in column A then

1. Select column A by clicking its heading label.

2. Data|Filter|Advanced Filter...|Copy to another location|Copy to: G1|Unique records only|OK

You may then want to sort column G.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Code						Code
2	A1	1	5	2	3		A1	31
3	B5	2	6	3	3		B5	37
4	C1	3	7	4	3		C1	33
5	A1	4	8	5	3		C2	16
6	B5	5	9	6	3		A2	19
7	C2	6	1	6	3		B1	22
8	A2	7	2	7	3
9	B1	8	3	8	3
10	C1	9	4	0	3
11
SUMPRODUCT

 

[Eisaz]

Peter you're a star! Thank you very much, this is exactly what i need.