SUMIF

[ExcelRoy]

Hi,

Can i please ask for some help, i am looking for a formula to show a sum of column C if E4 is matched in column D and F24 is matched in column E

Any help appreciated

Thanks

 

[Dave Patton]

I am not clear on your challenge.
The information below shows two formulas; you can try either or both.
I converted your extract to a Table.
You can use appropriate headings for the table.

T202008a.xlsm
	A	B	C	D	E
1			2	OldGray	2
2	ColumnA	Amt	ColumnC	ColumnD	ColumnE
3	885-1	1	3957	OldGray	Antenna 4H (Pic)
20	886-1	1	3957	OldGray	Antenna 4H (Pic)
38
3b

Cell Formulas
Range	Formula
C1	C1	=SUMIFS(B3:B200,D3:D200,D1,E3:E200,F1)
E1	E1	=SUBTOTAL(9,Table1[Amt])

 

[Toadstool]

ExcelRoy2.xlsx
	A	B	C	D	E	F
1	From B
2	885-1
3	886-1
4	889-1				3957
24						OldGray
37		885-1	1	3957	OldGray	Antenna 4H (Pic)
38		885-1	2	3062a	TrBlue	Brick 1 x 1 Round with Solid Stud (Pic)
39		885-1	1	3633	OldGray	Fence 1 x 4 x 1 (Pic)
40		885-1	1	3838	Red	Minifig Accessory Airtanks (Pic)
41		885-1	1	3626ap01	Yellow	Minifig Head with Solid Stud and Standard Grin Pattern (Pic)
42		885-1	1	970c00	Red	Minifig Hips and Legs (Complete) (Pic)
43		885-1	1	973p90	Red	Minifig Torso with Classic Space Pattern (Pic)
44		885-1	2	3024	TrRed	Plate 1 x 1 (Pic)
45		885-1	1	3839a	OldGray	Plate 1 x 2 with Handles Type 1 (Pic)
46		885-1	1	3460	OldGray	Plate 1 x 8 (Pic)
47		885-1	2	3022	OldGray	Plate 2 x 2 (Pic)
48		885-1	1	3034	OldGray	Plate 2 x 8 (Pic)
49		885-1	1	3298p90	OldGray	Slope Brick 33 3 x 2 with Classic Space Logo Pattern (Pic)
50		885-1	1	3933	OldGray	Wing 4 x 8 Left (Pic)
51		885-1	1	3934	OldGray	Wing 4 x 8 Right (Pic)
52		885-1	1	3829	OldGray	~Moved to 3829c01 (Pic)
53		885-1	1	193a	Red	~Moved to 3842a (Pic)
54		886-1	1	3957	OldGray	Antenna 4H (Pic)
55		886-1	1	3062a	TrGreen	Brick 1 x 1 Round with Solid Stud (Pic)
56		886-1	1	3838	OldGray	Minifig Accessory Airtanks (Pic)
57		886-1	1	3838	White	Minifig Accessory Airtanks (Pic)
58		886-1	1	3959	OldGray	Minifig Accessory Torch (Pic)
59		886-1	1	3626ap01	Yellow	Minifig Head with Solid Stud and Standard Grin Pattern (Pic)
60		886-1	1	970c00	White	Minifig Hips and Legs (Complete) (Pic)
61		886-1	1	973p90	White	Minifig Torso with Classic Space Pattern (Pic)
62		886-1	1	3794	OldGray	Plate 1 x 2 with 1 Stud (Pic)
63		886-1	2	122c01	OldGray	Plate 2 x 2 with Wheels in Red (Pic)
64		886-1	1	3020	OldGray	Plate 2 x 4 (Pic)
65		886-1	1	3795	OldGray	Plate 2 x 6 (Pic)
66		886-1	1	3039	OldGray	Slope Brick 45 2 x 2 (Pic)
67		886-1	4	3641	Black	Tyre (Pic)
68		886-1	1	3829	OldGray	~Moved to 3829c01 (Pic)
69		886-1	1	193a	White	~Moved to 3842a (Pic
70		889-1	1	3956	OldGray	Bracket 2 x 2 - 2 x 2 (Pic)
71		889-1	2	3787	OldGray	Car Mudguard 2 x 4 without Studs (Pic)
72		889-1	2	3957	OldGray	Toadstool test data
Sheet1 (2)

Cell Formulas
Range	Formula
A37:A72,A24,A2:A4	A2	=IF(COUNTIFS($D$1:$D$9999,$E$4,$E$1:$E$9999,$F$24)<ROW()-ROW($A$1),"",INDEX($B$1:$B$9999,AGGREGATE(15,6,ROW($B$1:$B$9999)/(($D$1:$D$9999=$E$4)*($E$1:$E$9999=$F$24)),ROW()-ROW($A$1))))

 

[ExcelRoy]

Hi Fluff,

E4 is a selection from Column D (Item) and F24 is a selection from column E (Colour)

Thanks

 

[Fluff]

Does Toadstool's formula give you what you want?