COUNTIFS the Last 500 Rows in a Column

[dv0x]

Hi Ninjas,

I'm trying to count the number of cells, within the last 500 rows of column G, that meet 2 criteria using COUNTIFS, MATCH, and INDEX.

How can I correctly combine these 2 formulas?

1. =COUNTIFS(G:G,">=" & Y11,G:G,"<=" & Z11)
2. =COUNTIFS(INDEX(G:G,MATCH(9.99999999999999E+307,G:G)-499):INDEX(G:G,MATCH(9.99999999999999E+307,G:G)))

Thank you

 

[NARAYANK991]

Hi ,

Your first formula uses the entire column for the range parameters ; the second formula narrows down the range to the last 500 rows of data in column G.

To combine both , try replacing references to the entire column G in the first formula , by the narrowed down referencing in the second formula , as follows :

=COUNTIFS(INDEX(G:G,MATCH(9.99999999999999E+307,G:G)-499):INDEX(G:G,MATCH(9.99999999999999E+307,G:G)),">="& Y11,INDEX(G:G,MATCH(9.99999999999999E+307,G:G)-499):INDEX(G:G,MATCH(9.99999999999999E+307,G:G)),"<="& Z11)

Narayan

 

[Hui]

=COUNTIFS(INDEX(G:G,MATCH(9.99999999999999E+307,G:G)-499):INDEX(G:G,MATCH(9.99999999999999E+307,G:G)),">=" & Y11,INDEX(G:G,MATCH(9.99999999999999E+307,G:G)-499):INDEX(G:G,MATCH(9.99999999999999E+307,G:G)),"<=" & Z11)

 

[dv0x]

Of course!

Thank you so much!