# Repeating sum formula every fourth block of rows

1. ## Repeating sum formula every fourth block of rows

Excel Gurus-
I am trying to create a macro/formula that would allow me to sum a column's value for four rows and give me a ratio. I need this repeated every row with the denominator being locked every four rows (sum). After the fourth row, the denominator changes +4 for the next four rows, and so forth. Example attached and below. Noticed the last two columns.

I am trying to get a distribution of population for each zip code. Zip codes are always shown in 4 rows each. Every zip code has four rows. SO, i am trying to take the current rows value for "New Patient" and divide that by the sum of all patients for that zip code. Repeat etc. I showed you the formula I am using, but need the denominator to stay the same for every four rows, then +4 each on the 5th row, +4 each on the 9th row etc.

I hope this is clear...i am trying to create a pattern the formula will show this below. Check the attachment for a cleaner look

thanks!

Member Zip Member Age Gender New Patients AllPatients Zip dist FORMULA BEHIND SCENE
48001 45-65 M 331 750 0.29774 =E2/SUM(\$E\$2:\$E\$5)
48001 18-44 F 37 547 0.21715 =E3/SUM(\$E\$2:\$E\$5)
48001 45-65 F 267 832 0.33029 =E4/SUM(\$E\$2:\$E\$5)
48001 18-44 M 38 390 0.15482 =E5/SUM(\$E\$2:\$E\$5)
48002 18-44 F 13 193 0.23565 =E6/SUM(\$E\$6:\$E\$9)
48002 45-65 F 74 230 0.28083 =E7/SUM(\$E\$6:\$E\$9)
48002 18-44 M 28 168 0.20513 =E8/SUM(\$E\$6:\$E\$9)
48002 45-65 M 101 228 0.27839 =E9/SUM(\$E\$6:\$E\$9)
48003 18-44 F 26 379
48003 18-44 M 31 309
48003 45-65 M 160 386
48003 45-65 F 131 430  Register To Reply

2. ## Re: Repeating sum formula every fourth block of rows

In E2:

=E3/SUMIF(A:A,A3,E:E)

Copy down. The SUMIF totals Col E when Col A has the same zip code as stated in that row.  Register To Reply

3. ## Re: Repeating sum formula every fourth block of rows

THANKS!!!!
The formula was a bit off but I adjusted for it to work. You rock!

=E2/SUMIF(A:A,A2,E:E)  Register To Reply

4. ## Re: Repeating sum formula every fourth block of rows

Hi and welcome to the forum Try this, copied down...

=E2/SUM(INDIRECT("E"&(ROW()-2-MOD(ROW()-2,4))+2&":E"&(ROW()-2-MOD(ROW()-2,4))+5))

It assume your data starts on row 2...adjust the 2 and 5 accordingly if it doesnt

edit: well duh, I didnt see that the "4" was based on column A - great catch, Harribone   Register To Reply

5. ## Re: Repeating sum formula every fourth block of rows

Like your solution though! Ain't a clue how it works mind, I think its time I expand my knowlegde some more.

Sorry sjshah82 I told you right formula but also told you wrong cell to put it in. Oops!  Register To Reply

6. ## Re: Repeating sum formula every fourth block of rows

try this ``Please Login or Register  to view this content.``  Register To Reply

##### Users Browsing this Thread

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

#### Tags for this Thread #### 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