Calculating average number of words in two different groups

Hello. I need to calculate the average number of words in the cells for two columns to see if the average number of words they entered differs between two groups.
I believe I could just use the formula to count the number of words in each column (C and D) and divide it by the number of cells that contain words. But the problem is that the original data has over 6000 participants (i.e. 6000 rows) so it is highly likely that some participants just didn’t enter any words for some reason (see C15/D16, which are both empty). So if I were to just count the number of words in the column divided by cells with words, those participants wouldn’t be counted. There is also the matter of seemingly empty cells that might contain spaces.

Is there a way for me to count the average number of words for each group while still counting the participants that might not have entered any words?

I attached an example file since the original data is not to be shared, and is needlessly large for this purpose anyway, but that is approximately how the data looks. Each row in the example file represents one participant/individual, and each individual is only a part of one of two groups.

I actually don’t know if this is even possible to do in excel, but if it is, I would be extremely grateful for any help you could offer.

would something like the below work for you?

E1:
=SUMPRODUCT((TRIM(C$2:C$20)<>"")+LEN(TRIM(C$2:C$20))-LEN(SUBSTITUTE(TRIM(C$2:C$20);" ";"")))/COUNTIF($A$2:$A$20;COLUMNS($E1:E1))
copied to F1

above would return 2.3 for Group 1 [23/10], and 2.22 for Group 2 [20/9]

use of TRIM to remove superfluous spaces.

note: assumed ; delimiter

per OP......

Is there a way for me to count the average number of words for each group while still counting the participants that might not have entered any words?

Originally Posted by XLent

would something like the below work for you?

E1:
=SUMPRODUCT((TRIM(C$2:C$20)<>"")+LEN(TRIM(C$2:C$20))-LEN(SUBSTITUTE(TRIM(C$2:C$20);" ";"")))/COUNTIF($A$2:$A$20;COLUMNS($E1:E1))
copied to F1

above would return 2.3 for Group 1 [23/10], and 2.22 for Group 2 [20/9]

use of TRIM to remove superfluous spaces.

note: assumed ; delimiter

This seems to be it! Thank you so much!
I had encountered similar formulas while googling my problem but I think most of them were missing the "/COUNTIF"-part. It's very hard to find the exact formula you need just by googling. Or maybe I'm just awful at googling.