Trying to Sum Subtotals to get Division Factor

alinedarc

New Member
Joined
Jan 12, 2017
Messages
1
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.

ClientDay of WeekDate of ReviewCount of Date of ReviewSubtotals by day of the weekDivision Factor
Hospital 1Sunday7 AM1582%
Hospital 1Sunday8 AM358
Hospital 1Sunday9 AM358
Hospital 1Sunday10 AM358
Hospital 1Sunday11 AM658
Hospital 1Monday7 AM1058
Hospital 1Monday8 AM1558
Hospital 1Monday9 AM358
Hospital 1Monday10 AM558
Hospital 1Monday11 AM758
Hospital 1Tuesday7 AM858
Hospital 1Tuesday8 AM1158
Hospital 1Tuesday9 AM1258
Hospital 1Tuesday10 AM258
Hospital 1Tuesday11 AM458

<tbody>
</tbody><colgroup><col><col><col><col><col><col></colgroup>
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
ClientDay of WeekDate of ReviewCount of Date of Review
Hospital 1Sunday7:00 AM1
Hospital 1Sunday8:00 AM3
Hospital 1Sunday9:00 AM3
Hospital 1Sunday10:00 AM3
Hospital 1Sunday11:00 AM6
Hospital 1Monday7:00 AM10
Hospital 1Monday8:00 AM15
Hospital 1Monday9:00 AM3
Hospital 1Monday10:00 AM5
Hospital 1Monday11:00 AM7
Hospital 1Tuesday7:00 AM8
Hospital 1Tuesday8:00 AM11
Hospital 1Tuesday9:00 AM12
Hospital 1Tuesday10:00 AM2
Hospital 1Tuesday11:00 AM4
Sunday16
Monday40
Tuesday37
formula giving 16
=SUMPRODUCT(($B$2:$B$16=B23)*($D$2:$D$16))
but not really clear what you want

<colgroup><col span="3"><col><col span="2"></colgroup><tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,652
Messages
5,597,373
Members
414,139
Latest member
okela0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top