Column word count in Excel

Recently I have been analysing survey data which has been extracted from an online survey instrument into an Excel sheet. The analysis has proved very entertaining. However, there are some key elements of formula which I have needed some expert help with.

I often need a word count for comments, as one of many measures to attempt to establish what a 'quality' response is. I have worked this out in the past (and written about it here), and getting that information requires using formulas in Excel (or VBA if I knew how that worked!), but this time I needed something a little different.  

I needed to get a sum of all the text in column for checking where I had pulled multiple data sets off the original data, and wanted to validate the multiple data sets. My formula last time worked perfectly in rows, but wouldn't work to sum a column of words in Excel. Despite multiple Google searches, it took me quite some time to found a formula which kinda sorta worked. And, once I had the rudiments, I then had to do quite a lot of experimentation to get this to work properly. 

Do attached is a formula which sums the words in a single Excel column (the example below sums all words in column G, from row 12 to row 525):

=SUM(LEN(G12:G525)-LEN(SUBSTITUTE(G12:G525," ","")))

Now all I need to do is to find a formula which will allow me to change the value with a sub-total...

Sam

Reference: Microsoft Support (2022). Description of formulas to count the occurrences of text, characters, and words in Excel. https://docs.microsoft.com/en-us/office/troubleshoot/excel/formulas-to-count-occurrences-in-excel