If all sheets have similar name this ARRAY formula works.

In A2 then drag down.

<sheet5!$f$8,0,sum(countif(indirect("sheet"&row($1:$5)&"!a1:n10"),sheet5!$f$8))))[ code]

Code:

`=SMALL(Sheet1:Sheet5!$A$1:$N$10,ROWS($1:1)+IF(SMALL(Sheet1:Sheet5!$A$1:$N$10,ROWS($1:1))< Sheet5!$F$8,0,SUM(COUNTIF(INDIRECT("Sheet"&ROW($1:$5)&"!A1:N10"),Sheet5!$F$8))))`

<sheet5!$f$8,0,rows($1:1)+sum(countif(indirect("sheet"&row($1:$5)&"!a1:n10"),sheet5!c8))))[ code]

How ARRAY formula is entered

Paste Formula in the cell.

Press F2

Hold Shift+ Ctrl Keys and hit Enter key.

Now the formula is surrounded by {} by excel.</sheet5!$f$8,0,rows($1:1)+sum(countif(indirect("sheet"&row($1:$5)&"!a1:n10"),sheet5!c8))))[></sheet5!$f$8,0,sum(countif(indirect("sheet"&row($1:$5)&"!a1:n10"),sheet5!$f$8))))[>