sumif

[Salamullah]

I have sales data with area & volume with sales value for both the years I want to bring total in the respective sales area, need formula when I will add new data it will automatically give results.


[TABLE="width: 352"]
<tbody>[TR]
[TD]Data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Year[/TD]
[TD]Volume[/TD]
[TD]Sales[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]550[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]550[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]2900[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Result required[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Volume[/TD]
[TD="colspan: 2"]Sales[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]850[/TD]
[TD="align: right"]850[/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]1450[/TD]
[TD="align: right"]1450[/TD]
[/TR]
</tbody><colgroup><col><col span="4"></colgroup>[/TABLE]

 

[Peter_SSs]

Is this what you mean?
The formula in G3 is copied across to H3 and down to row 4.
The formula in I3 is copied across to J3 and down to row 4.

Book1
	A	B	C	D	E	F	G	H	I	J
1		Year	Volume	Sales			Volume		Sales
2	A	2018	1	200			2018	2017	2018	2017
3	B	2018	2	300		A	4	12	600	600
4	A	2018	3	400		B	6	14	850	850
5	B	2018	4	550		TOTAL	10	26	1450	1450
6	A	2017	5	200
7	B	2017	6	300
8	A	2017	7	400
9	B	2017	8	550
SUMIFS

Cell Formulas
Range	Formula
G3	=SUMIFS($C$2:$C$1000,$A$2:$A$1000,$F3,$B$2:$B$1000,G$2)
I3	=SUMIFS($D$2:$D$1000,$A$2:$A$1000,$F3,$B$2:$B$1000,I$2)

 

[Salamullah]

Yes perfect, thanks

 

[Peter_SSs]

Yes perfect, thanks

No problem. Glad it helped.