Sumif ,but only upto where last 3 instances match

[ste33uka]

Hi i have added a imige for better understanding,
Would anyone have a formula to sum column C,
when G1 matches column A and H1 matches column B, but only the last 3 instances where they match.
So in example answer would be 14.
Thanks

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F
1					Dog	apple
2					35
3
4
5	dog	apple	3
6	cat	pear	4
7	frog	pear	5
8	frog	plum	6
9	cat	apple	7
10	dog	apple	8
11	dog	plum	9
12	cat	pear	10
13	horse	apple	11
14	dog	apple	12
15	frog	plum	13
16	cat	apple	14
17	dog	apple	15
18
Data

Cell Formulas
Range	Formula
E2	E2	=SUM(INDEX($C$5:$C$21,AGGREGATE(14,6,(ROW($C$5:$C$21)-ROW($C$5)+1)/($A$5:$A$21=$E$1)/($B$5:$B$21=$F$1),{1,2,3})))

In future please post sample data using the XL2BB add-in, it saves us from having to create something ourselves.

 

[jasonb75]

Try this one, array confirmed with Ctrl Shift Enter.

=SUMPRODUCT(SIGN(IFERROR(AGGREGATE(14,6,ROW(A5:A21)/(A5:A21=G1)/(B5:B21=H1),{1,2,3}),0)),C5:C23)

Might work, might not, can't test it on a screen capture, need XL2BB sample for that.

 

[ste33uka]

How about

+Fluff New.xlsm
	A	B	C	D	E	F
1					Dog	apple
2					35
3
4
5	dog	apple	3
6	cat	pear	4
7	frog	pear	5
8	frog	plum	6
9	cat	apple	7
10	dog	apple	8
11	dog	plum	9
12	cat	pear	10
13	horse	apple	11
14	dog	apple	12
15	frog	plum	13
16	cat	apple	14
17	dog	apple	15
18
Data

Cell Formulas
Range	Formula
E2	E2	=SUM(INDEX($C$5:$C$21,AGGREGATE(14,6,(ROW($C$5:$C$21)-ROW($C$5)+1)/($A$5:$A$21=$E$1)/($B$5:$B$21=$F$1),{1,2,3})))

In future please post sample data using the XL2BB add-in, it saves us from having to create something ourselves.

Thanks alot, im going to learn about xl2bb right now, thanks again

 

[ste33uka]

Hi as a first test of xl2bb is this correct ?

del.xlsx
	E
2
Sheet1

 

[Fluff]

You ned to select the cells you want to show.

 

[ste33uka]

del.xlsx
	A	B	C	D	E	F	G	H
1					14		apple	mark
2
3
4
5	apple	mark	12
6	apple	mark	12
7	apple	terry	12
8	apple	mark	12
9	apple	terry	12
10	apple	stephen	12
11	apple	stephen	12
12	apple	stephen	12
13	apple	stephen	1
14	apple	mark	1
15	apple	mark	1
16	apple	mark	12
17	apple	terry	7
18	apple	mark	1
19	pear	mark	1
20	orange	terry	9
21	orane	mark	1
Sheet1

Cell Formulas
Range	Formula
E1	E1	=SUM(INDEX($C$5:$C$21,AGGREGATE(14,6,(ROW($C$5:$C$21)-ROW($C$5)+1)/($A$5:$A$21=$G$1)/($B$5:$B$21=$H$1),{1,2,3})))

is this correct?

 

[Fluff]

That's right

 

[ste33uka]

example1.xlsx
	A	B	C	D	E	F	G	H
1					14		apple	mark
2
3
4
5	apple	terry	12
6	apple	stephen	12
7	apple	terry	12
8	apple	mark	5
9	apple	terry	12
10	apple	stephen	12
11	apple	stephen	12
12	apple	stephen	12
13	apple	stephen	1
14	apple	mark	1
15	apple	mark	1
16	apple	mark	12
17	apple	terry	7
18	apple	mark	1
19	pear	mark	1
20	orange	terry	9
21	orane	mark	1
22
23
24
25
Sheet1

Cell Formulas
Range	Formula
E1	E1	=SUM(INDEX($C$4:$C$777,AGGREGATE(14,6,(ROW($C$4:$C$777)-ROW($C$4)+1)/($A$4:$A$21=$G$1)/($B$4:$B$21=$H$1),{1,2,3})))

Hi the 1st example i have uploaded works fine,
But second example as there is not 3 matches i get value '#num!'
Is there a way it could just sum the last 2 instances in the second example?
Thanks

example2.xlsx
	A	B	C	D	E	F	G	H
1					#NUM!		apple	mark
2
3
4
5
6
7
8
9
10
11
12
13	pear	mark	1
14	orange	terry	9
15	orane	mark	1
16	apple	mark	12
17	apple	terry	7
18	apple	mark	1
19	pear	mark	1
20	orange	terry	9
21	orane	mark	1
22
23
Sheet1

Cell Formulas
Range	Formula
E1	E1	=SUM(INDEX($C$4:$C$777,AGGREGATE(14,6,(ROW($C$4:$C$777)-ROW($C$4)+1)/($A$4:$A$21=$G$1)/($B$4:$B$21=$H$1),{1,2,3})))

 

[jasonb75]

This one works

=SUMPRODUCT((ROW(A5:A21)=IFERROR(AGGREGATE(14,6,ROW(A5:A21)/(A5:A21=G1)/(B5:B21=H1),{1,2,3}),0))*C5:C21)