# Formula to Divide '1' by individual values and return the SUM to a Cell

1. ## Formula to Divide '1' by individual values and return the SUM to a Cell

G'day Folks

I have many Workbooks where Sheet1! Col H have individual values on many rows.
I want to divide 1 by each value individually and return the SUM of those new values to Sheet2! Cell H1.

Sample Workbook attached.

Thankum  Register To Reply

2. ## Re: Formula to Divide '1' by individual values and return the SUM to a Cell

Formula:  `Please Login or Register  to view this content.`

BSB  Register To Reply

3. ## Re: Formula to Divide '1' by individual values and return the SUM to a Cell

Thanks BSB..could you point the formula to sheet 1 for me thanks.  Register To Reply

4. ## Re: Formula to Divide '1' by individual values and return the SUM to a Cell

Formula:  `Please Login or Register  to view this content.`

BSB  Register To Reply

5. ## Re: Formula to Divide '1' by individual values and return the SUM to a Cell

Okay, we are wearing it down now One more thing with it....
Say I wanted to use a set range in the formula eg H2:H2000 but some of the cells in that range will be blank...can we make that work?  Register To Reply

6. ## Re: Formula to Divide '1' by individual values and return the SUM to a Cell

Perhaps something like...
Formula:  `Please Login or Register  to view this content.`

BSB  Register To Reply

7. ## Re: Formula to Divide '1' by individual values and return the SUM to a Cell

Try:

=SUM(IFERROR(1/Sheet1!H2:H2000,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.  Register To Reply

8. ## Re: Formula to Divide '1' by individual values and return the SUM to a Cell

Champions one and all!
Both solutions work!
Thanks to you both.

Cheers
hammer  Register To Reply

9. ## Re: Formula to Divide '1' by individual values and return the SUM to a Cell

Happy to help.

Thanks for the rep point BSB  Register To Reply