OFFSET and COUNTIF

[pwill]

Hi can anyone help with a formula?


I have rows of Data on sheet2 and there are Headers in the first row.

On sheet1, I have a formula in cell "T5" that is '[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=COUNTIF('Sheet2'!B:H,0) and works fine.

And in cell "AA3" on sheet1, I have a count of the number of rows used on sheet2, starting from the second row as there are headers on the first row. (This count is added through VBA so there is no formula in "AA3").

For example there might be 100 rows of data on sheet2 "A2:Z101" and the formula in cell "T5" on sheet1 will count the number of 0's found in columns "B:H" on sheet2.

Is there a way to use the COUNTIF formula with OFFSET? so the COUNTIF starts counting from a desired row on sheet2 when the value in Cell "AA3" on Sheet1 changes.

eg if the value in "AA3" is 100 then the COUNTIF formula uses all the rows in "B:H" on sheet2 but if the value in Cell "AA3" changes to 80 then I want to OFFSET 20 rows on sheet2

(minus the headers)​

so the COUNTIF formula starts at row 21 and only counts the number of '0s' found in the remaining 80 rows.
[/FONT]
Or if the value in Cell "AA3" changes to 90 then OFFSET 10 rows so the

COUNTIF formula would start at row 11 on sheet2 and so on...

Any help would be appreciated

regards

pwill
​

 

[Tetra201]

Maybe

=COUNTIF(OFFSET(Sheet2!B2:H101,100-AA3,,AA3),0)

or a non-volatile version

=COUNTIF(INDEX(Sheet2!B:B,102-AA3):Sheet2!H101,0)

 

[pwill]

Maybe

=COUNTIF(OFFSET(Sheet2!B2:H101,100-AA3,,AA3),0)

or a non-volatile version

=COUNTIF(INDEX(Sheet2!B:B,102-AA3):Sheet2!H101,0)

Hi Tetra201 thanks for your reply, much appreciated , however I should have said the number of rows on sheet2 also change as data gets added, is there a way to have your formula be dynamic so it does the calculation when more rows are added to sheet2?

 

[pwill]

Hi Tetra201 thanks for your reply, much appreciated , however I should have said the number of rows on sheet2 also change as data gets added, is there a way to have your formula be dynamic so it does the calculation when more rows are added to sheet2?

I managed to worked it out and this does what I'm after Thanks for your help Tetra201 much appreciated.

=COUNTIF(INDEX('Sheet2'!B:B,COUNTA('Sheet2'!A:A)-AA3):'Sheet2'!H:H,0)

pwill

 

[pwill]

I managed to worked it out and this does what I'm after Thanks for your help Tetra201 much appreciated.

=COUNTIF(INDEX('Sheet2'!B:B,COUNTA('Sheet2'!A:A)-AA3):'Sheet2'!H:H,0)

pwill

This way seems to work better though:

=COUNTIF(OFFSET('Sheet2'!B:H,COUNTA('Sheet2'!A:A)-AA$3,,AA$3),0)

pwill