Named Dynamic Range and Countifs formula

Good evening all,

I am having an issue with a formula. I need to count the blank cells in a column which is determined by the length of a different column (data is being extracted from a database), so I have added an offset into the refers to in name manager. This works absolutely fine for all formula that calculate on the range using SUMIF and SUM, but when I try to include it in a Countifs formula I am getting a #!Value error.

Can anyone point me in the right direction. I have attached the spreadsheet. The errors are showing in cells P22:P27, and the range that I have (attempted) to make dynamic is called SEN.

Any help would be gratefully appreciated!

Hi,

You are using Dynamic Named Range (DNR) for "SEN" which automatically determines the size of the array.
You are not using a DNR for your "percentatt". There for the two arrays are different lengths.

Use this DNR formula for your "percentatt".

Please Login or Register  to view this content.

Hope that makes sense and works for you.

Cheers

Hi Southward

Thank you very much for coming back to me so quickly!! I've made the change that you suggested, which works absolutely fine for the "SEN" rows but unfortunately it throws out all of the other formulae in the <90 percent columns!
I then tried putting the same offset into all of the other named ranges being used, but that completely skewed the data for some reason!

Any more ideas?

Ali

Did you modify the DNR that I provided so that it recognizes the correct column in the "student data" sheet?

Attached is a version of your file with updated DNR's.

Cheers

Hi Southward,

You are amazing!! Thank you so much, the spreadsheet is working perfectly now.

You are very welcome. Glad I could be of some assistance.

Cheers