Hi Tigger, hi Tony,

first of all, Vba offers far better solutions (see Tony's user defined function)

Just for fun I've integrated the former formula in order to accomplish new duties as assigned.

It's not very elegant. I've underline the patch.

=SUMPRODUCT(--(ISNUMBER(FIND(" "&ROW($1:$50)&","," "&I2&","))))+SUMPRODUCT(ISNUMBER(FIND({"2-6","8-12","17-21","23-27","2-12","17-27","3-6","8-11","18-21","19-20","24-25","11-12","17-25","23-24","26-27","2-4","4-11","1-12"},I2))*{5,5,5,5,11,11,4,4,4,2,2,2,9,2,2,3,8,12})

So if you'd need to add  1-100:

=SUMPRODUCT(--(ISNUMBER(FIND(" "&ROW($1:$50)&","," "&I2&","))))+SUMPRODUCT(ISNUMBER(FIND({"2-6","8-12","17-21","23-27","2-12","17-27","3-6","8-11","18-21","19-20","24-25","11-12","17-25","23-24","26-27","2-4","4-11","1-12","1-100"},I2))*{5,5,5,5,11,11,4,4,4,2,2,2,9,2,2,3,8,12,100})

Tony's udf handles automatically assigned duties and it's more robust.

Cheers

Many thanks, Sunny - I've tested on a copy of live data, and all looks great, including week 1, weeks in term 3, and the various oddball combinations 🙂 If you are ever in London, let me know - lunch is on me for the time saved 🙂

Thanks also to everyone who offered thoughts on this - it is all much appreciated. I should have some extra time available now to try and understand what the code does!

Have a great day,

Tony.