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
SUM
Name	qty	price	Name	qty	price	Name	qty	price	Name	qty	price
john	carl	antony	john
peter	francis	peter	carl
mary	antony	john	antony
john	mary	peter
peter	francis
mary

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]120[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]60[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]70[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]60[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]N4[/TH]
[TD="align: left"]=IF(M4="","",SUMIF($A$4:$I$20,M4,$B$4:$J$20))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]O4[/TH]
[TD="align: left"]=IF(M4="","",SUMIF($A$4:$I$20,M4,$C$4:$K$20))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]M4[/TH]
[TD="align: left"]{=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"),)&""}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

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