SUMIF for reciprocals

1
votes

In excel, I am looking to calculate the sum of reciprocals for each number in the column, starting from that number.

Below works fine:

Ex: Below formula for each of the cell in second column

=SUM(1/A1:A6)
=SUM(1/A2:A6)
.
.

Pic with no nulls

But if I have a column with no value then it wont as the it treats nulls as zero and gets a divide by zero error.

PicExcel

If I use =SUMIF(1/J2:J6, ">0") then it's not working.

Any help to achieve this please?

2
excelexcel-formula
You can use something like this and drag down from the first row: =SUM(IF(A1:$A$6<>"", 1/A1:$A$6, 0)) - MacroMarc

2 Answers

2
votes

You can use IFERROR:

=SUM(IFERROR(1/A1:A6,0))

1
votes

It looks like you can use dynamic arrays so =SUM(FILTER(1/A1:A6,A1:A6<>0)) should work.