Limit formula to first 10 times a one criteria is found in a range?

[jack109]

Hi all

Is there a way various formulas (AVERAGEIF, SUMIF, COUNTIF etc) can be used so that they only include the last 10 times the one of the criteria's is found?

For example

The main criteria to use is APPLES

=COUNTIFS(A:A,"APPLES",B:B,"YES")

Apples could appear 30 times in column A but I only want column B to count "YES" first 10 times that "APPLES" appear in column A. The newest data will always at the top of column A.


Thanks

 

[gaz_chops]

Welcome to the forum,

I'm sure you will get a better solution, but this appears to work

=IFERROR(COUNTIFS(INDIRECT("A1:A"&INDEX(ROW(A1:A50),SMALL(IF((A1:A50="Apples")*(B1:B50="Yes"),ROW(A1:A50)),10))),"Apples",INDIRECT("B1:B"&INDEX(ROW(A1:A50),SMALL(IF((A1:A50="Apples")*(B1:B50="Yes"),ROW(A1:A50)),10))),"Yes"),COUNTIFS(A1:A50,"APPLES",B1:B50,"YES"))

Enter as an array using Ctrl, Shift & Enter

 

[gaz_chops]

If you are only using Count, then try this

=MIN(10,COUNTIFS(A:A,"APPLES",B:B,"YES"))

 

[gaz_chops]

For sumif

=SUMIFS(INDIRECT("c1:c"&INDEX(ROW(A1:A50),SMALL(IF((A1:A50="Apples")*(B1:B50="Yes"),ROW(A1:A50)),10))),INDIRECT("A1:A"&INDEX(ROW(A1:A50),SMALL(IF((A1:A50="Apples")*(B1:B50="Yes"),ROW(A1:A50)),10))),"Apples",INDIRECT("B1:B"&INDEX(ROW(A1:A50),SMALL(IF((A1:A50="Apples")*(B1:B50="Yes"),ROW(A1:A50)),10))),"Yes")

Again enter as an array

 

[jack109]

Thanks for the reply

Sorry, only tried the first formula and its not producing the results I expect. It is returning 10 for apples when I know the result should be 1.

 

[gaz_chops]

Post the example you are using. I take it you are using Count?

 

[jack109]

Yes, Im trying count at the moment.

From the example below "APPLES" appears 15 times. However, the first ten times "APPLES" appears, only 3 times does it match "YES" in column B. The result I am getting is 5, which is the total times "APPLES" and "YES" match.

 

[gaz_chops]

I misunderstood & looked for the last 10 with Apple & Yes.

Try
In a new cell enter the following, as an array formula

=INDEX(ROW(A:A),SMALL(IF((A:A="Apples"),ROW(A:A)),MIN(10,COUNTIFS(A:A,"APPLES"))))

This will give the row no. of the 10th match

Then enter this to give the count

=COUNTIFS(INDIRECT("A1:A"&G4),"Apples",INDIRECT("B1:B"&G4),"Yes")

G4 is the cell that i have the array formula in, change it to the cell ref you used

 

[jasonb75]

I wouldn't even attempt that with a single formula,

Book1
	A	B	C	D	E
1	PEARS	NO		Criteria 1	APPLES
2	APPLES	NO		Criteria 2	YES
3	PEARS	NO		Limit	10
4	PEARS	NO		Last row	33
5	ORANGES	YES		Result	5
6	PEARS	YES
7	APPLES	YES
8	PEARS	NO
9	ORANGES	NO
10	PEARS	NO
11	APPLES	NO
12	APPLES	YES
13	PEARS	NO
14	APPLES	NO
15	PEARS	YES
16	ORANGES	NO
17	PEARS	YES
18	PEARS	NO
19	PEARS	NO
20	APPLES	NO
21	PEARS	YES
22	PEARS	NO
23	ORANGES	NO
24	APPLES	YES
25	ORANGES	NO
26	PEARS	NO
27	PEARS	YES
28	ORANGES	YES
29	ORANGES	YES
30	APPLES	NO
31	ORANGES	NO
32	APPLES	YES
33	APPLES	YES
34	ORANGES	YES
35	PEARS	NO
36	APPLES	NO
37	APPLES	NO
38	APPLES	YES
39	PEARS	YES
40	ORANGES	NO
41	APPLES	YES
42	ORANGES	NO
43	PEARS	YES
44	ORANGES	NO
45	PEARS	YES
46	ORANGES	YES
47	APPLES	YES
48	APPLES	YES
49	APPLES	NO
50	ORANGES	NO
51	PEARS	YES
52	PEARS	NO
53	APPLES	NO
54	PEARS	YES
Sheet10

Cell Formulas
Range	Formula
E4	E4	=AGGREGATE(15,6,ROW(A1:A54)/(A1:A54=E1),E3)
E5	E5	=COUNTIFS(A1:INDEX(A:A,E4),E1,B1:INDEX(B:B,E4),E2)

 

[jack109]

Excellent! Cheers Guys!!!