Average Ignoring Blank Columns

Really struggling with formula on a calculated column on my SharePoint list for some reason!

I am trying to get the average of 3 columns sometimes one will be blank some times there will be no blanks.

Here is the formula I have:

``````=IF(COUNT([Column1],[Column2],[Column3])="","",SUM([Column1],[Column2],[Column3])/COUNT([Column1],[Column2],[Column3]))
``````

On this test Column 2 is empty and this returns #DIV/0!

Any help would be gratefully received.

New contributor
E.Keogh is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
• All 3 columns are "Number" columns? can you add the screenshot of list view (for sample data to test) to your question if possible? Aug 4 at 10:35
• Hey, @GaneshSanap, Yes number columns, apologies, its a simple scoring system of 1,2 or 3. on some occasions it will be blank though. Aug 4 at 10:43
• Try my answer given below. Let me know if it works for you. Aug 4 at 10:58

``````=IF(COUNT([Column1],[Column2],[Column3])=0,0,SUM([Column1],[Column2],[Column3])/COUNT([Column1],[Column2],[Column3]))
• When you use the choice columns, retuned value of column will be text and not number. In such cases, use VALUE function like: `VALUE([Column1])` and `VALUE([Column2])`. But it's always better to use the Number columns for calculations. Aug 4 at 11:41