Nested COUNTIF in SUM formula

I am using the following to be able to count the number of unique texts within a column.

{=SUM(1/COUNTIF(C4:C792,C4:C792))}

I would like the above formula to include the range C4:C872 instead of C4:C792, but due to the fact that C793:C872 are blank cells the output of the formula becomes #DIV/0!

Is there anyway that I can edit the above formula so that it does not give this error but includes the range with the blank cells?

Hello and welcome to the forum.

Try this:

=SUM(IF(C4:C872<>"",1/COUNTIF(C4:C872,C4:C872))) Ctrl Shift Enter

edit: too slow... ;-)

adjust numerator and append 2nd range with ""

{=SUM((C4:C872<>"")/COUNTIF(C4:C872,C4:C872&""))}

or

=SUMPRODUCT((C4:C872<>"")/COUNTIF(C4:C872,C4:C872&""))

latter doesn't require Array entry - but no more efficient.

Thank you both. :D i have used =SUMPRODUCT((C4:C875<>"")/COUNTIF(C4:C875,C4:C875&""))

as i keep forgetting to do CTRL SHIFT ENTER afterwards