sumproduct formula result incorrect

Originally Posted by boopathiraja

Hi, Check out for cell D1 and L6 that too duplicates

Typo: L1. And that is the root cause of the problem.

But even when L2 is cleared as it should be, note that =MATCH(A1,A2,0) returns #N/A. This is because A1 and A2 are infinitesimally different due to arithmetic anomalies that arise by summing B1:Q1/COUNTIF(...).

If that is a concern, one remedy is to round the SUMPRODUCT to the precision that you require. For example:

=ROUND(SUMPRODUCT(B1:Q1,1/COUNTIF(B1:Q1,B1:Q1)),3)

rounds to 1 percentage decimal place.

Also note the change in the second COUNTIF parameter. I don't see any value is writing B1:Q1&"".

Caveat lector: B1:Q1/COUNTIF(B1:Q1,B1:Q1) does not work because the seemingly empty cells in B1:Q1 are not truly empty. For example, ISBLANK(I1) returns FALSE. Presumably, Bob did copy-and-paste-special-value of null strings into I1 et al.