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

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)

--
Biff
Microsoft Excel MVP


"jlclyde" wrote in message
...
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

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

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:B50)*(C1:C5="this"),A1:A5*1/B1:B5))

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


--
Biff
Microsoft Excel MVP


"jlclyde" wrote in message
...
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