interval calculation across several sheet over a range of cells help!!

Hi

 

I currently have a workbook with 3 different sheets which I need to tally up info from.  I currently have the sheet self calculating if there is any information showing in a range of cells which is working perfectly. This is my sum:

=(COUNTIF(‘3 term 17’!B4:E9,”<>”&””)+COUNTIF(‘3 term 17’!J4:M9,”<>”&””)+COUNTIF(‘3 term 17’!R4:U9,”<>”&””)+COUNTIF(‘3 term 17’!Z4:AC9,”<>”&””)+COUNTIF(‘4 term 17’!B4:E9,”<>”&””)+COUNTIF(‘4 term 17’!J4:M9,”<>”&””)+COUNTIF(‘4 term 17’!R4:U9,”<>”&””)+COUNTIF(‘4 term 17’!Z4:AC9,”<>”&””)+COUNTIF(‘5 term 17’!B4:E9,”<>”&””)+COUNTIF(‘5 term 17’!J4:M9,”<>”&””)+COUNTIF(‘5 term 17’!R4:U9,”<>”&””)+COUNTIF(‘5 term 17’!Z4:AC9,”<>”&””))

When I drag this formula into cells it automatically calculates however the cell reference is +1 cell instead of +5 which I want it to be as the data I need to analyse is in blocks of 4 columns and five rows.  I have tried to work out OFFSET and INDEX command but am totally struggling. 

 

Any help would be most appreciated.

 

Thanks in advance

By: fiona

Leave a Reply

Your email address will not be published. Required fields are marked *