Sum based on weekdays in a given month

[rrmando]

Hello everyone. Need help on coming up with a formula that will add/sum the rates per day based on the # of days in a given month.

In the example below, the daily rates are set. If I take September as an example, Sept. 1st falls on a Thursday and Sept. 30 on a Friday (using =WEEKDAY(B11,1) to come up with the days). If i were looking at a calendar, I need to add the daily rates beginning with Thursday Sept. 1 up through Friday Sept. 30.

Any suggestions? I'm having a difficult time visualizing how it would work. I'm thinking beginning with the 1st day of the month, add the # of days in that particular month and have the formula cycle through the rate range somehow? Thank you in advance.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Monday</td><td style=";">Tuesday</td><td style=";">Wednesday</td><td style=";">Thursday</td><td style=";">Friday</td><td style=";">Saturday</td><td style=";">Sunday</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">100</td><td style="text-align: right;;">125</td><td style="text-align: right;;">100</td><td style="text-align: right;;">150</td><td style="text-align: right;;">100</td><td style="text-align: right;;">125</td><td style="text-align: right;;">200</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style=";">September</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">9/1/2011</td><td style="text-align: right;;">Thursday</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">9/30/2011</td><td style="text-align: right;;">Friday</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

 

[Sal Paradise]

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Sunday</td><td style=";">Monday</td><td style=";">Tuesday</td><td style=";">Wednesday</td><td style=";">Thursday</td><td style=";">Friday</td><td style=";">Saturday</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Rates:</td><td style="text-align: right;;">200</td><td style="text-align: right;;">100</td><td style="text-align: right;;">125</td><td style="text-align: right;;">100</td><td style="text-align: right;;">150</td><td style="text-align: right;;">100</td><td style="text-align: right;;">125</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style=";">Sunday</td><td style=";">Monday</td><td style=";">Tuesday</td><td style=";">Wednesday</td><td style=";">Thursday</td><td style=";">Friday</td><td style=";">Saturday</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">Sep 2011</td><td style="text-align: right;;">800</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td><td style="text-align: right;;">400</td><td style="text-align: right;;">750</td><td style="text-align: right;;">500</td><td style="text-align: right;;">500</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">Oct 2011</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">500</td><td style="text-align: right;;">500</td><td style="text-align: right;;">400</td><td style="text-align: right;;">600</td><td style="text-align: right;;">400</td><td style="text-align: right;;">625</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">Nov 2011</td><td style="text-align: right;;">800</td><td style="text-align: right;;">400</td><td style="text-align: right;;">625</td><td style="text-align: right;;">500</td><td style="text-align: right;;">600</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">Dec 2011</td><td style="text-align: right;;">800</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td><td style="text-align: right;;">400</td><td style="text-align: right;;">750</td><td style="text-align: right;;">500</td><td style="text-align: right;;">625</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">Jan 2012</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">500</td><td style="text-align: right;;">625</td><td style="text-align: right;;">400</td><td style="text-align: right;;">600</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">Feb 2012</td><td style="text-align: right;;">800</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td><td style="text-align: right;;">500</td><td style="text-align: right;;">600</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">Mar 2012</td><td style="text-align: right;;">800</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td><td style="text-align: right;;">400</td><td style="text-align: right;;">750</td><td style="text-align: right;;">500</td><td style="text-align: right;;">625</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">Apr 2012</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">500</td><td style="text-align: right;;">500</td><td style="text-align: right;;">400</td><td style="text-align: right;;">600</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">May 2012</td><td style="text-align: right;;">800</td><td style="text-align: right;;">400</td><td style="text-align: right;;">625</td><td style="text-align: right;;">500</td><td style="text-align: right;;">750</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">Jun 2012</td><td style="text-align: right;;">800</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td><td style="text-align: right;;">400</td><td style="text-align: right;;">600</td><td style="text-align: right;;">500</td><td style="text-align: right;;">625</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">Jul 2012</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">500</td><td style="text-align: right;;">625</td><td style="text-align: right;;">400</td><td style="text-align: right;;">600</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">Aug 2012</td><td style="text-align: right;;">800</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td><td style="text-align: right;;">500</td><td style="text-align: right;;">750</td><td style="text-align: right;;">500</td><td style="text-align: right;;">500</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">Sep 2012</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td><td style="text-align: right;;">400</td><td style="text-align: right;;">600</td><td style="text-align: right;;">400</td><td style="text-align: right;;">625</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">WEEKDAY(<font color="Green">ROW(<font color="Purple">INDIRECT(<font color="Teal">$A5&":"&DATE(<font color="#FF00FF">YEAR(<font color="Navy">$A5</font>),MONTH(<font color="Navy">$A5</font>)+1,0</font>)</font>)</font>)</font>)=COLUMN(<font color="Green">A2</font>)</font>)</font>)*B$2</td></tr></tbody></table></td></tr></table><br />

 

[rrmando]

Awesome..thank you Sal! Can you explain how the weekday works in your formula? How does it know how many days in a given month? Thanks again!

 

[barry houdini]

You could also get the same results with this formula

=INT((WEEKDAY($A5-COLUMNS($A2:A2))+DAY(EOMONTH($A5,0))-1)/7)*B$2

 

[Sal Paradise]

It creates an array of each day in the month.

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A5&":"&DATE(YEAR($A5),MONTH($A5)+1,0))))=COLUMN(A2)))*B$2

Let's break that down.

ROW(INDIRECT($A5&":"&DATE(YEAR($A5),MONTH($A5)+1,0)))

This gives the rows from the date in column A, to the last day in the month.

The last day of the month is determined with this:
DATE(YEAR($A5),MONTH($A5)+1,0)

Take the date in A5, add a month, and go to day 0 (the last day of the preceding month).

Once we have those rows, we wrap them in the "Weekday" formula, which gives an array of weekdays (5,6,7,1,2,3,4,5,6,7,etc.). We say, "If the weekday equals 1" using this part:
=COLUMN(A2)

When you move the formula to the right, the 1 turns to 2, then 3, etc. So you get it to give you the number of days that match that weekday.

We multiply that by the rates at the top to get your answer.