# 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.

• 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, 2022 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, 2022 at 10:43
• Try my answer given below. Let me know if it works for you. Aug 4, 2022 at 10:58

If all 3 columns are of type "Number", use below formula:

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

I just tried it & it works for me: Documentation: Examples of common formulas in lists

• Thank you very much, I am however still getting a zero in average. I will list some more detail below. Column one and Column Three a choice of 1,2,3 Column two is a calculated column which has divided by two a choice column of 1,2,3,4,5,6 that gives an answer of 1,2 or 3 When Column Two is blank on mine I get 0 as the average using your formula Aug 4, 2022 at 11:29
• I have changed choice to Number and fixed it all Thank you very much @ganeshSanap Aug 4, 2022 at 11:37
• 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, 2022 at 11:41