Sumproduct Errors

[Tikey]

I have been using the following to sum the number of valuesin a list without counting the same value twice and ignoring any cells wherethere is no data.

=SUMPRODUCT(($V$17:$V$40<>"")/COUNTIF($V$17:$V$40,$V$17:$V$40&""))

This works fine, but in some cells I could have errors ie#VALUE! and this creates an error.

How can the function be modified to ignore any errors incells and also not be restricted to a range of cells ( ie V17:V40 in the above) but cover a whole column ?

Thanks

 

[Ron Coderre]

This formula seems to do what you want:

=SUMPRODUCT(ISNUMBER($V:$V)/COUNTIF($V:$V,$V:$V&""))

However....NEVER perform an operation on every cell in a column. There are ways to avoid doing that. Typically, using Excel Tables.

 

[Tikey]

Ron Thanks very much for your reply which works fine on just numbers,but my post wasn’t clear enough, as the values contain letters as well asnumbers which causes the formula to fail. Is there a way of dealing with both in one cell ie SE345678 and still avoiding any errors ? You also mention never performing a task on every cell in acolumn, if this is to be avoided, can the formula automatically adjust itselfto cover a change in the range of cells or would this have to be done manually? Thanks for any help you can suggest. ​