SUMSIF formula help in a different way

[zone709]

Trying to get total results from columns B & D in F & G

Need to pull in Column F from Column B the IDS just once not duping it then use maybe =SUMSIF to get my total results from column F & D in G. This is an example sheet as my numbers go way down the sheet.

The ID column below is column B

Excel 2016 (Windows) 32 bit

	B​	C​	D​	E​	F​	G​
1​	ID		Gross Pay		ID	Results
2​	5​		$ 2,500.00​		5​	$ 4,638.00​
3​	6​		$ 1,300.00​		6​	$ 1,300.00​
4​	2​		$ 1,250.00​		2​	$ 4,933.32​
5​	2​		$ 1,233.32​		213​	$ 2,156.00​
6​	213​		$ 2,156.00​		23​	$ 230.00​
7​	23​		$ 230.00​		10​	$ 1,223.00​
8​	5​		$ 2,113.00​		11​	$ 4,512.00​
9​	10​		$ 5,454.00​		7​	$ 3,600.00​
10​	6​		$ 8,956.00​
11​	11​		$ 4,512.00​
12​	10​		$ 1,223.00​
13​	2​		$ 1,200.00​
14​	2​		$ 1,250.00​
15​	5​		$ 25.00​
16​	7​		$ 3,600.00​

Sheet: Sheet1

 

[alansidman]

Use a pivot table to pivot your data. Put the ID in rows and Gross Pay in Values.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	1	ID		Gross Pay		ID	Results
2	2	5		£2,500.00		5	£4,638.00
3	3	6		£1,300.00		6	£10,256.00
4	4	2		£1,250.00		2	£4,933.32
5	5	2		£1,233.32		213	£2,156.00
6	6	213		£2,156.00		23	£230.00
7	7	23		£230.00		10	£6,677.00
8	8	5		£2,113.00		11	£4,512.00
9	9	10		£5,454.00		7	£3,600.00
10	10	6		£8,956.00
11	11	11		£4,512.00
12	12	10		£1,223.00
13	13	2		£1,200.00
14	14	2		£1,250.00
15	15	5		£25.00
16	16	7		£3,600.00
Budget

Cell Formulas
Range	Formula
F2:F9	F2	=UNIQUE(FILTER(B2:B1000,B2:B1000<>""))
G2:G9	G2	=SUMIFS(D:D,B:B,F2#)
Dynamic array formulas.

 

[zone709]

Hi when i enter those formals it keeps returning SPILL. Not sure why

 

[Fluff]

In that case clear all the cells below the two formulae.

 

[zone709]

Thank you guys Fluff it works. Did what you told me to thanks

 

[Fluff]

Glad to help & thanks for the feedback.