Count the Sumproduct of two arrays (one column and one row)

[Deleven]

Hello

Could someone please explain how to count a sumproduct of two arrays? I need to count the number of cells that are not 0 after calculating a sumproduct.

The COUNTIFS formula below (see screenshot) does not work. #VALUE ! is returned.



It is just a testing sample. Of course, I would have to apply the formula to a far more complex dataset.

However, in this sample, the result should be:
A1*C1 = 1*0.5 = 0.5 > 0 = 1
A2*D1 = 1*0 = 0
A3*E1 = 0*0.25 = 0
A4*F1 = 1*0.85 = 0.85 > 0 = 1
A5*G1 = 0*0.88 = 0
A6*H1 = 1*0.46 = 0.46 >0 = 1

So the final result of the count should be 3.

How is this accomplished with a formula?

Thank you very much for the help.

Best,
Daniel

 

[Special-K99]

You'd do better to post more than one row of test data and to explain in words what you're trying to achieve.

 

[Deleven]

I went just over it again and found out that it would also be possible to skip the SUMPRODUCT and just use a COUNTIFS with two criteria.

The value in the column as well as in the row has to unequal 0.

This would be the formula: =COUNTIFS($A$1:$A$6;"<>0";$C1:$H1;"<>0")

I would have to transpose the row data:

=COUNTIFS($A$1:$A$6;"<>0";TRANSPOSE($C1:$H1);"<>0")

However, the TRANSPOSE does not seem to work in a COUNTIFS formula. Is there any other alternative to using the TRANSPOSE in COUNTIFS?