Trying to Sum Subtotals to get Division Factor

[alinedarc]

Hello,

I have the date below (just a sample). I used =SUMIFS(Count_of_date,client,"Bronx-Lebanon",day_of_the_week,{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"}) to try to get the subtotals for each day of the week on column E and then I would divide that subtotal by the number of reviews for each time in the day to get a "division factor". My ultimate goal is to get to the division factor, but I can not go passed the fact that it is not summing by day of the week - all days return me 58 reviews when that's the sum for Sunday. If I get this to work, I could even try to divide D for the working formula and get the division factor straight. Any help will be appreciated! Maybe there is a much easier way to do this.

[TABLE="width: 670"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Day of Week[/TD]
[TD]Date of Review[/TD]
[TD]Count of Date of Review[/TD]
[TD]Subtotals by day of the week[/TD]
[TD]Division Factor[/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Sunday[/TD]
[TD]7 AM[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]2%[/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Sunday[/TD]
[TD]8 AM[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]58[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Sunday[/TD]
[TD]9 AM[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]58[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Sunday[/TD]
[TD]10 AM[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]58[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Sunday[/TD]
[TD]11 AM[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]58[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Monday[/TD]
[TD]7 AM[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]58[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Monday[/TD]
[TD]8 AM[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]58[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Monday[/TD]
[TD]9 AM[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]58[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Monday[/TD]
[TD]10 AM[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]58[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Monday[/TD]
[TD]11 AM[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]58[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Tuesday[/TD]
[TD]7 AM[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]58[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Tuesday[/TD]
[TD]8 AM[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]58[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Tuesday[/TD]
[TD]9 AM[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]58[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Tuesday[/TD]
[TD]10 AM[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]58[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Tuesday[/TD]
[TD]11 AM[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]58[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col></colgroup>[/TABLE]

 

[oldbrewer]

[TABLE="width: 466"]
<colgroup><col span="3"><col><col span="2"></colgroup><tbody>[TR]
[TD]Client[/TD]
[TD]Day of Week[/TD]
[TD]Date of Review[/TD]
[TD]Count of Date of Review[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Sunday[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Sunday[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Sunday[/TD]
[TD="align: right"]9:00 AM[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Sunday[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Sunday[/TD]
[TD="align: right"]11:00 AM[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Monday[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Monday[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Monday[/TD]
[TD="align: right"]9:00 AM[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Monday[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Monday[/TD]
[TD="align: right"]11:00 AM[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]9:00 AM[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hospital 1[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]11:00 AM[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sunday[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Monday[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Tuesday[/TD]
[TD="align: right"]37[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]formula giving 16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]=SUMPRODUCT(($B$2:$B$16=B23)*($D$2:$D$16))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]but not really clear what you want[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]