If any of the cells in the selected range contain an error like #DIV0, #NA, #VALUE your result will not calculate correctly unless you include an error trap or correct your formulas so that they thae care of the error calculation.

In Formulas you can use the IFERROR()

I suspect your UDF does something that is not allowed for a function called from a sheet. If you post the code, we can probably tell you what.

Many thanks for looking into my issue, my code is as follows:

Function GETCOLORCOUNT(CountRange As Range, CountColor As Range) As Single

Dim CountColorValue As Long
Dim TotalCount As Single
Dim rCell As Range
Application.Volatile

CountColorValue = CountColor.Interior.Color

Set rCell = CountRange

For Each rCell In CountRange

If rCell.DisplayFormat.Interior.Color = CountColorValue Then
TotalCount = TotalCount + 1
Else
End If
Next rCell

GETCOLORCOUNT = TotalCount

End Function

You can't use DisplayFormat in a function called from a cell. The easiest solution is usually to use something like COUNTIF(S) together with whatever criteria are being used by the conditional formatting.

I was able to remove DisplayFormat from the function. The updated code gives the same, correct Formula Result as before in the Function Arguments window (which is 5), but it is now showing 0 in the cell.

I was unsure how to rewrite the code using COUNTIF.  The updated code is as follows:

Function GETCOLORCOUNT(CountRange As Range, CountColor As Range) As Single

Dim CountColorValue As Long
Dim TotalCount As Single
Dim rCell As Range
Application.Volatile

CountColorValue = CountColor.Interior.Color

Set rCell = CountRange

For Each rCell In CountRange
If rCell.Interior.Color = CountColorValue Then
TotalCount = TotalCount + 1
Else
End If
Next rCell

GETCOLORCOUNT = TotalCount

End Function

Are the cells coloured through conditional formatting, or do they have colours applied to them manually?

The cells that are being counted are coloured using Conditional Formatting.  I have removed this and applied the colouring manually, and the function now works, amazing!  

I assume I should work on rewriting the code using Countifs, unless you have any other suggestions?

Thank you so much for your help, if this function works it will save us a huge amount of time.  I work in a secondary school and we are currently doing the colour counting using VBA code that is run using a button. But on some of our spreadsheets, which are used by all our teachers for reporting assessment results to our students, the code takes a long time to run.

No, what I'm suggesting is using a regular COUNTIF(S) function without any code. If you can apply the same criteria that are used to colour the cells, you don't need code at all. And your function will always be up to date, unlike pretty much any code version.

Many thanks for your reply, apologies for not getting back to you sooner, it has been a very busy week.

I have just tried to work out how to do this using COUNTIF but have not got anywhere with it.  Are you able to send me an example formula?  

If  you can give an example of the criteria used for the conditional formatting, I will see what I can do.

That's amazing thank you, here are the criteria:

I don't know if this helps but I have also attached an example of a row for one student. 

I am determining the colour counters in the first 3 cells.

For the green count:

=SUMPRODUCT(--(O13:s13/O$7:S$7>=0.75))

For the red (first):

=SUMPRODUCT(--(O13:s13/O$7:S$<0.5))

For the balance, the simplest option is just to count the numbers in the range - COUNT(O13:S13) - and then subtract the results of the other two calculations.

This is brilliant, it all works, thank you so much! 

I added another column to count the number of blank cells to calculate the middle/amber cells correctly.   

This will save us a lot of time 🙂