group tables and values

[Manolocs]

Hello, I am looking for a formula to achieve this and can not be a macro.
I have three tables with 3 columns each and different row lengths, need to group all columns by names and make sum of the values on the SUM table like the follow:

[/IMG]

Is it possible without macro?
Thanks in advance for your help.

 

[frabulator]

This might be what you are looking for.

It is the SumIf function. Basically it will check to see if your criteria matches and then it will sum up the totals.
You will probably need to reorganize your charts a little. What I did is stacked your years together in columns A, B and C and then added the sums in columns F, G and H.
I then added in the follow code to the 'qtty' column of the sum table and the 'price' column.

'qtty
=SUMIF(A:A,F3,B:B)

'price
=SUMIF(A:A,F3,C:C)


--- EDIT

basically it checks all of column A to see if it matches the name in 'F3' and if it does it sums all of the matches in either column 'B' or 'C'.

--- END EDIT

hope that helps!
~Frab

 

[Eric W]

Here's one option:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2		2014				2015				2016				SUM
3	Name	qty	price		Name	qty	price		Name	qty	price		Name	qty	price
4	john	1	20		carl	3	50		antony	1	15		john	8	120
5	peter	1	20		francis	4	30		peter	1	40		carl	3	50
6	mary	1	30		antony	1	45		john	1	20		antony	2	60
7					john	6	80		mary	1	30		peter	7	70
8					peter	5	10						francis	4	30
9													mary	2	60
10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula N4 =IF(M4="","",SUMIF($A$4:$I$20,M4,$B$4:$J$20)) O4 =IF(M4="","",SUMIF($A$4:$I$20,M4,$C$4:$K$20)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula M4 {=INDIRECT(TEXT(MIN(IF(ISTEXT($A$4:$I$20)*(COUNTIF($M$3:M3,$A$4:$I$20)=0),ROW($4:$20)*100+COLUMN($A:$I),7^8)),"R0C00"),)&""} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The array formula in M4 creates a list of unique names, but not sorted. I'll look at that some more.