Excel 2007 : Variable Range in Countif

[themaster25]

My spreadsheet I am referencing can have a varying number of rows in Sheet1. What I am doing is using =COUNTA(A:A) in Cell D1 in Sheet 2 cell to get the number of rows. What I need to do then from Sheet3 do a COUNTIF that references Col A in Sheet1 and needs to incorporate the value I obtained in Sheet 2 D1 to define the range. It would look something like this:

=COUNTIF(Sheet1!$A1:EndRange, XXXXXX)

I need to know how to put the value from Sheet 2 D1 to define the EndRange above.

[Marcol]

If you are looking to count text strings

Please Login or Register  to view this content.

For numbers use

Please Login or Register  to view this content.

[themaster25]

Actually, what I'm wanting is how would I put Sheet2!$D1 to define my range in, i.e. EndRange Below.:

=COUNTIF(Sheet1!$A1:$AEndRange, XXXXXX)

for instance, if Sheet2!$D1 has value 270, I would need the formula to evaluate to:

=COUNTIF(Sheet1!$A1:$A275, XXXXXX)

Thanks.

[Andrew-R]

Something along the lines of:

=COUNTIF(INDIRECT("Sheet1!$A1:$A" & Sheet2!$D1),XXXXXX)

Although I'm confused why your example says D1 holds 270, but your formula counts to A275.

[themaster25]

Sorry, a mistype. Yes, it should be 275. Thanks.

[Marcol]

I would think it better to avoid volatile functoins such as INDIRECT(), whereever possible
Try

Please Login or Register  to view this content.

[themaster25]

Thanks Andrew-R, that worked perfectly, exactly what I needed.