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

jack109

Board Regular
Joined
May 10, 2020
Messages
50
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
I wouldn't even attempt that with a single formula,
Book1
ABCDE
1PEARSNOCriteria 1APPLES
2APPLESNOCriteria 2YES
3PEARSNOLimit10
4PEARSNOLast row33
5ORANGESYESResult5
6PEARSYES
7APPLESYES
8PEARSNO
9ORANGESNO
10PEARSNO
11APPLESNO
12APPLESYES
13PEARSNO
14APPLESNO
15PEARSYES
16ORANGESNO
17PEARSYES
18PEARSNO
19PEARSNO
20APPLESNO
21PEARSYES
22PEARSNO
23ORANGESNO
24APPLESYES
25ORANGESNO
26PEARSNO
27PEARSYES
28ORANGESYES
29ORANGESYES
30APPLESNO
31ORANGESNO
32APPLESYES
33APPLESYES
34ORANGESYES
35PEARSNO
36APPLESNO
37APPLESNO
38APPLESYES
39PEARSYES
40ORANGESNO
41APPLESYES
42ORANGESNO
43PEARSYES
44ORANGESNO
45PEARSYES
46ORANGESYES
47APPLESYES
48APPLESYES
49APPLESNO
50ORANGESNO
51PEARSYES
52PEARSNO
53APPLESNO
54PEARSYES
Sheet10
Cell Formulas
RangeFormula
E4E4=AGGREGATE(15,6,ROW(A1:A54)/(A1:A54=E1),E3)
E5E5=COUNTIFS(A1:INDEX(A:A,E4),E1,B1:INDEX(B:B,E4),E2)

Is there a way that the AGGREGATE formula can be modified so that whilst it limits it to the first 10, if there are less than 10 occasions if will return that value.

EG.

Apples only appears 7 times in the colum,, so instead of the formula returning #NUM it returns 7?

Cheers
 
Upvote 0
Try changing the aggregate formula to

=IFERROR(AGGREGATE(15,6,ROW(A1:A54)/(A1:A54=E1),E3),MATCH("ZZZ",A:A))
 
Upvote 0
Maybe a longshot but can this formula be used to across two columns?

=IFERROR(AGGREGATE(15,6,ROW(A1:A54)/(A1:A54=E1),E3),MATCH("ZZZ",A:A))


So it would be A1:B54? "Apples" could appear in column A four times and column B 6 times or column A 7 times n column B 3 times. As long as it returns the last row that "APPLES" cumulatively appears 10 times across the two columns.
 
Upvote 0
Ive just changed the range from A1:B54 and it works. Surely it wasn't that simple. I've sensed checked it manually and can't see no errors.
 
Upvote 0
Surely it wasn't that simple.
Sometimes it is.

The only anomaly would be in the event of something like below, where in theory only 1 entry in row 15 should be counted. This only happens when all 'APPLES' in the limit range meet the 'YES' criteria, just one entry for 'APPLES' with 'NO' would calculate as normal.

This could be 'fixed' if needed by simply setting the countifs formula to show the minimum of the formula result or the limit, i.e. =MIN(COUNTIFS(A1:INDEX(B:B,F4),F1,C1:INDEX(D:D,F4),F2),F3)

Book2
ABCDEF
1PEARSPEARSNONOCriteria 1APPLES
2APPLESAPPLESYESYESCriteria 2YES
3PEARSAPPLESNOYESLimit10
4ORANGESPEARSNONOLast row15
5ORANGESORANGESNONOResult11
6PEARSORANGESNONO
7ORANGESPEARSNONO
8ORANGESPEARSNONO
9APPLESAPPLESYESYES
10APPLESORANGESYESNO
11APPLESPEARSYESNO
12PEARSORANGESNONO
13APPLESPEARSYESNO
14ORANGESAPPLESNOYES
15APPLESAPPLESYESYES
Sheet9
Cell Formulas
RangeFormula
F4F4=AGGREGATE(15,6,ROW(A1:B54)/(A1:B54=F1),F3)
F5F5=COUNTIFS(A1:INDEX(B:B,F4),F1,C1:INDEX(D:D,F4),F2)
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top