# 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

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

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.

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

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?

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

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.

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

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1