Sumproduct to divide

[jlclyde]

I am trying to write a formula using Sumproduct to dived data. I
understand that the reciprocal of a number multiplied is the same as
dividing so Sumproduct shoudl work. the problem is that there are
blanks in the data and then it is diving by one.
=Sumproduct(A1:A5,1/B1:B5) This works fine if there are no 0 or
blanks.

Thanks,
Jay

 

[T. Valko]

Try this array formula** :

Assuming the range will contain *numbers only*, no text, no formula blanks.

=SUM(IF(B1:B5<>0,A1:A5*1/B1:B5))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[jlclyde]

Biff,
I have another part to the same question. What happens if there is a
third variable present? Say C olumn must also match. C is text.
Thanks,
Jay

 

[T. Valko]

If I understand you...

Array entered:

=SUM(IF((B1:B5<>0)*(C1:C5="this"),A1:A5*1/B1:B5))

If your numbers in column B are *always* positive:

=SUM(IF((B1:B5>0)*(C1:C5="this"),A1:A5*1/B1:B5))

I'm more "comfortable" using >0 rather than <>0.