Add Formulas to Subtotal Rows 1

[jw45]

I have the following data that I have added subtotals to get several totals for each week time period. Within each week I also need to know how many different ODs occur and how many different Balls occur. See highlighted numbers.

In the example below I used the formula

=SUMPRODUCT(1/COUNTIF(C4:C4,C4:C4))

but I have to copy the formula to the next week then manually adjust the range for that week.

Can someone help me with code to add this automatically copy and update the range through VB or other??


Week Qty OD ID Ball
200517 265 6.25 3.50 0.0000
200517 Total 265 [COLOR=red yellow]1 1[/color]
200525 2 8.27 4.84 0.4687
200525 28 8.27 5.51 0.5000
200525 150 6.25 3.00 0.5000
200525 40 7.75 4.50 0.5625
200525 49 10.24 3.46 0.5625
200525 Total 269 [COLOR=red yellow]4 3[/color]
200526 141 5.92 4.25 0.2813
200526 100 9.01 5.99 0.8125
200526 70 8.00 5.01 0.8125
200526 200 8.00 5.01 0.8125
200526 50 8.27 4.72 0.8750
200526 50 10.24 6.30 0.2813
200526 300 6.63 4.00 0.8750
200526 Total 911 [COLOR=red yellow]6 3[/color]
200527 400 5.13 3.24 0.4687
200527 55 6.25 4.00 0.5000
200527 60 5.13 2.75 0.5000
200527 90 6.25 3.00 0.5625
200527 75 10.24 6.30 0.5625
200527 265 6.25 3.50 0.4687
200527 Total 945 [COLOR=red yellow] [/color]
200528 265 6.25 3.50 0.0000
200528 99 5.92 4.25 0.0000
200528 175 7.00 4.50 0.0000
200528 30 7.75 5.00 0.0000
200528 50 5.13 2.75 0.0000
200528 500 6.25 3.25 0.0000
200528 Total 1119

 

[macropod]

Hi jw45,

For your qty column (B), and given that your first sub-total row is row 3, you could use:
=SUMIF($A$1:A2,$A2,B$1:B2)
in B3. Simply copy & paste to each sub-total row and the formula will update the ranges to suit.

For your OD column (C), you could use:
=SUMPRODUCT((OFFSET(B$1,MATCH($A2,$A$1:$A2,0)-1,):B2<>0)/COUNTIF(OFFSET(B$1,MATCH($A2,$A$1:$A2,0)-1,):B2, OFFSET(B$1,MATCH($A2,$A$1:$A2,0)-1,):B2))
Again, simply copy & paste to each sub-total row and the formula will update. The same formula copied to your ID column (D) will work for that column also.

Cheers

 

[macropod]

Hi jw,
Slight correction to the second formula (it used the wrong column reference):
=SUMPRODUCT((OFFSET(C$1,MATCH($A2,$A$1:$A2,0)-1,):C2<>0)/COUNTIF(OFFSET(C$1,MATCH($A2,$A$1:$A2,0)-1,):C2,OFFSET(C$1,MATCH($A2,$A$1:$A2,0)-1,):C2))

Cheers

 

[KenWright]

Have you considered using a Pivot table, as they were designed with that type of data structure in mind, and mean you have to create no formulas whatsover. You can set your reports up just how you want them, update the source data and then just refresh the tables.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[jw45]

Thanks macropod..exactly what I needed/wanted.

Ken... I do not know how to make a pivot table to create the report in the format my customer wants. He wants the totals directly under the row data... not out to the right.