SUMIFS/Unique formula

[Add365]

Hi everyone,

Hope you can help, I created an entry tab with 4 columns of data that gets sorted and entered into another tab called unique, this basically filters down column A and sorts it numerically and shows the totals for column B,C & D relating to the certain number in A1, A2 etc. (Screenshot below,)

The Formula's I have used in the unique tab on the right are
Cell A2 =SORT(UNIQUE(Entry!A2:A1048576))
Cell B2 =SUMIFS(Entry!B$2:B$1048576,Entry!A$2:A$1048576,Unique!A2)
Cell C2 =SUMIFS(Entry!C$2:C$1048576,Entry!A$2:A$1048576,Unique!A2)
Cell D2 =SUMIFS(Entry!D$2:D$1048576,Entry!A$2:A$1048576,Unique!A2)
and dragged the formula down in cells B,C, & D

This all works fine but now I need to add 2 more columns in as per below

I need the unique tab to work as it did before but also filtering column B & C aswell as Column A.
I have changed the formula in Cell A2 to =SORT(UNIQUE(Entry!A2:C1048576)) so it sorts columns A,B & C. (not sure if this is the best way to do it)?
But I need to know what to change the formulas in B2,C2,D2 etc as it is looking at Column A and adding the net weight, gross weight & value up everytime it see's the same number in column A. I need it to work it out correctly, I have done a manual example below of how it should look when its worked out correctly.

Help please.

Thank you

 

[Peter_SSs]

For the future, you might investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Have you considered using Excel's built-in Pivot Table feature (tabular form)?

Add365.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	CNN	Country	Pref	NW	GW	Value			CNN	Country	Pref	Sum of NW	Sum of GW	Sum of Value
2	3	UK	Y	100	100	100			1	UK	Y	100	100	100
3	2	DE	Y	100	100	100			1	USA	N	100	100	100
4	1	USA	N	100	100	100			1	USA	Y	200	200	200
5	1	UK	Y	100	100	100			2	DE	Y	200	200	200
6	1	USA	Y	100	100	100			3	UK	Y	100	100	100
7	1	USA	Y	100	100	100
8	2	DE	Y	100	100	100
9
Entry

A couple of other comments:
It is a bad idea to use the sheet name in the reference to a cell or range on the sheet that the formula is in.
Unless you really have massive data, I would recommend against using (virtually) whole column references. Just pick a number big enough to be sure t0 cover any data you might have (maybe 10,000?)

Cell B2 =SUMIFS(Entry!B$2:B$1048576,Entry!A$2:A$1048576,Unique!A2)

 

[Add365]

Hi Thanks for your help, but I will be pasting random data into the entry tab which I want my unique tab to condense down for me, so this can be uploaded to another program in that format.
All I need to work out is what formula to put in D2,E2,E2 so when it see's a code in colum A it doesnt just add the net weight, gross weight and value all together if column A has the same code in it twice or 3 times. I want it to filter it down more so it goes on the criteria of column A,B & C instead of just column A.

Can you let me know if this is possible as Im guessing it is just changing the formula a bit that I have in cells D2,E2,F2. Cell D2 = SUMIFS(Entry!D$2:D$1048576,Entry!A$2:A$1048576,Unique!A2)

I can use XL2BB if it helps?

 

[Peter_SSs]

I can use XL2BB if it helps?

Yes please. Give us that small dummy data set with your existing formulas and also the expected results with XL2BB.

 

[Add365]

Here you go

Export Multi Line Copy.xlsx
	A	B	C	D	E	F	G	H
1	CNN	Country of Origin	Preference Yes or No	Nett Weight	Gross Weight	VALUE	Running Total	£ 700.00
2	1234567890	UK	Y	100	100	100
3	1234567890	DE	Y	100	100	100
4	1111111111	USA	N	100	100	100
5	1111111111	UK	Y	100	120	100
6	1232222222	USA	Y	100	100	100
7	1111111111	UK	N	100	100	100
8	1111111111	UK	N	100	100	100
9
Entry

Cell Formulas
Range	Formula
H1	H1	=SUM(F2:F10000)

Cells with Data Validation
Cell	Allow	Criteria
A1:A9	Text length	=10

Export Multi Line Copy.xlsx
	A	B	C	D	E	F	G	H
1	For Col A - "CNN"	Country of Origin	Preference Yes or No	Nett Weight	Gross Weight	For Col C - "Value"	Running Total	£ 1,700.00
2	1111111111	USA	N	400	420	400
3	1111111111	UK	Y	400	420	400
4	1111111111	UK	N	400	420	400
5	1232222222	USA	Y	100	100	100
6	1234567890	UK	Y	200	200	200
7	1234567890	DE	Y	200	200	200
8	0	0	0	0	0	0
9				0	0	0
Unique

Cell Formulas
Range	Formula
H1	H1	=SUM(F2:F1048576)
A2:C8	A2	=SORT(UNIQUE(Entry!A2:C1048576))
D2:D9	D2	=SUMIFS(Entry!D$2:D$1048576,Entry!A$2:A$1048576,Unique!A2)
E2:E9	E2	=SUMIFS(Entry!E$2:E$1048576,Entry!A$2:A$1048576,Unique!A2)
F2:F9	F2	=SUMIFS(Entry!F$2:F$1048576,Entry!A$2:A$1048576,Unique!A2)
Dynamic array formulas.

 

[Add365]

This would be the expected results, which I have entered manually just to show an example of what I want the formulas in D2,E2 & F2 to do

Export Multi Line Copy.xlsx
	A	B	C	D	E	F	G	H
1	For Col A - "CNN"	Country of Origin	Preference Yes or No	Nett Weight	Gross Weight	For Col C - "Value"	Running Total	£ 700.00
2	1111111111	USA	N	100	100	100
3	1111111111	UK	Y	100	120	100
4	1111111111	UK	N	200	200	200
5	1232222222	USA	Y	100	100	100
6	1234567890	UK	Y	100	100	100
7	1234567890	DE	Y	100	100	100
8	0	0	0
Unique

Cell Formulas
Range	Formula
H1	H1	=SUM(F2:F1048576)
A2:C8	A2	=SORT(UNIQUE(Entry!A2:C1048576))
Dynamic array formulas.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	For Col A - "CNN"	Country of Origin	Preference Yes or No	Nett Weight	Gross Weight	For Col C - "Value"	Running Total	700
2	1111111111	USA	N	100	100	100
3	1111111111	UK	Y	100	120	100
4	1111111111	UK	N	200	200	200
5	1232222222	USA	Y	100	100	100
6	1234567890	UK	Y	100	100	100
7	1234567890	DE	Y	100	100	100
8
Data

Cell Formulas
Range	Formula
H1	H1	=SUM(F2#)
A2:C7	A2	=SORT(UNIQUE(FILTER(Entry!A2:C10000,Entry!A2:A10000<>"")))
D2:F7	D2	=SUMIFS(Entry!D2:D10000,Entry!$A2:$A10000,INDEX($A2#,,1),Entry!$B2:$B10000,INDEX($A2#,,2),Entry!$C2:$C10000,INDEX($A2#,,3))
Dynamic array formulas.

 

[Add365]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	For Col A - "CNN"	Country of Origin	Preference Yes or No	Nett Weight	Gross Weight	For Col C - "Value"	Running Total	700
2	1111111111	USA	N	100	100	100
3	1111111111	UK	Y	100	120	100
4	1111111111	UK	N	200	200	200
5	1232222222	USA	Y	100	100	100
6	1234567890	UK	Y	100	100	100
7	1234567890	DE	Y	100	100	100
8
Data

Cell Formulas
Range	Formula
H1	H1	=SUM(F2#)
A2:C7	A2	=SORT(UNIQUE(FILTER(Entry!A2:C10000,Entry!A2:A10000<>"")))
D2:F7	D2	=SUMIFS(Entry!D2:D10000,Entry!$A2:$A10000,INDEX($A2#,,1),Entry!$B2:$B10000,INDEX($A2#,,2),Entry!$C2:$C10000,INDEX($A2#,,3))
Dynamic array formulas.

Yes that is it, brilliant I knew it could be done just couldnt work out the formula.

Thanks so much for your help

 

[Fluff]

Glad we could help & thanks for the feedback.