SUMPRODUCT() results in an error

Hi, Sir,

I am using SUMPRODUCT() to count how many cells with values. In column A, the values are:

Date
01/11/2012
#N/A

The function I used is:

=SUMPRODUCT(--(LEN($A$1:$A$5)>0))

Which resulted in an error. How can make this work?

Thanks a lot.

Formula:

=SUMPRODUCT(IF(NOT(ISERROR($A$1:$A$5)),--(LEN($A$1:$A$5)>0),0))

entered with ctrl+Shift+enter

Originally Posted by Sindhus

Formula:

=SUMPRODUCT(IF(NOT(ISERROR($A$1:$A$5)),--(LEN($A$1:$A$5)>0),0))

entered with ctrl+Shift+enter

Is there anyway that we don't use array? It would be excellent.

Thanks for many helps.

Sorry that if SUMPRODUCT() is really to be used (as a good knowledge), how can I add another condition in this function to allow for cases with #NA? I tried to use:

=SUMPRODUCT(--(LEN($A$1:$A$5)>0),ISNUMBER($A$1:$A$5)) But not working

Originally Posted by daddylonglegs

Please indicate the results you expect, what do you want to count exactly? Technically an error value like #N/A is still a value, so to count all values use COUNTA

For the formula: =SUMPRODUCT(--(LEN($A$1:$A$5)>0),ISNUMBER($A$1:$A$5))

If there is an error #N/A in a cell, then the result is also #N/A. If there is an error #DIV/0!, then the result is also #DIV/0!

I just want to learn how to use SUMPRODUCT() function to allow for those cells with errors (i.e. they are not included for count).

Originally Posted by daddylonglegs

I'm still not clear what you want to count, that will make a difference to the formula you use. I understand you want to exclude errors.....but do you want to count numbers only.....or text and numbers......or something else?

If the answer is numbers only then I would use COUNT but if you want to use SUMPRODUCT you can use this version

=SUMPRODUCT(--ISNUMBER($A$1:$A$5))

.....but that doesn't include any text values (or logical values) in the count - do you need to include those too?

Yes, I want to count for both numbers and text values.

How to improve this formula to allow for cells with invalid values
Hi Sir,

I learned from the forum that we can use the formula below to calculate the total number of non-empty cells in a column A. But this formula does not allow cells with invalid values. That is, as long as some cells have errors (e.g., #N/A, #DIV/0!), then this formula will stop working.

=SUMPRODUCT(--(LEN($A$1:$A$5)>0))

Can you advise how to improve for this formula to allow for those cells with invalid values? Great to learn this. Thanks.

Originally Posted by daddylonglegs

I assume COUNTA is no good, Pepe because it will also count error values......on that basis the shortest way I know is to use this formula

=COUNT(1/(A1:A5<>""))

confirmed with CTRL+SHIFT+ENTER

.....but if you want a "normal" formula with SUMPRODUCT try

=SUMPRODUCT(ISNUMBER(MATCH((A1:A5<>"")+0,{1},0))+0)

Too good to be true. Well learned!