# Countif Weekday Array between multiple Dates

##### New Member
Hello,

I need to be able to create a formula in the DAY cells below that tally the number of MONDAYs, TUESDAYs, and WEDNESDAYs within the Searchable Cells according to the Date Range specified. I have attempted "=SUM(N(WEEKDAY(F4:F536)=2))" to start but I cannot figure out how to get it to focus its search between date ranges.

Searchable Cells Date Range
A
 04/01/2014 04/14/2014 05/01/2014 05/27/2014 06/05/2014 07/01/2014 08/16/201 09/01/2014

<tbody>
</tbody>

 05/15/2014 07/16/2014

<tbody>
</tbody>

 Monday Value Tuesday Value Wednesday Value Thursday Value Friday Value Saturday Value Sunday Value

<tbody>
</tbody>

Thank you for any assistance you can provide

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### Weazel

##### Well-known Member
Seems to work ok though there might be a better way....

in D2

=SUMPRODUCT((TEXT(WEEKDAY(\$A\$2:\$A\$9),"dddd")=C2)*(\$A\$2:\$A\$9>=\$A\$11)*(\$A\$2:\$A\$9<=\$B\$11))

 A B C D 1 Date Day Count 2 4/1/2014 Monday 0 3 4/14/2014 Tuesday 2 4 5/1/2014 Wednesday 0 5 5/27/2014 Thursday 1 6 6/5/2014 Friday 0 7 7/1/2014 Saturday 0 8 8/16/2014 Sunday 0 9 9/1/2014 10 11 5/15/2014 7/16/2014

<TBODY>
</TBODY>

#### VBA Geek

##### MrExcel MVP
<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: #BBB"><thead><tr style=" background-color: #DAE7F5;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: #DAE7F5;color: #161120">E1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">WEEKDAY(<font color="Green">\$A\$1:\$A\$8,2</font>)=ROWS(<font color="Green">D\$1:D1</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">01/04/2014</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Monday</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">14/04/2014</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Tuesday</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">01/05/2014</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Wednesday</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">27/05/2014</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Thursday</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">05/06/2014</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Friday</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">01/07/2014</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Saturday</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">16/08/2014</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Sunday</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">07/09/2014</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 #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet3</p><br /><br />

#### VBA Geek

##### MrExcel MVP
updated:

<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: #BBB"><thead><tr style=" background-color: #DAE7F5;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: #DAE7F5;color: #161120">E1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">WEEKDAY(<font color="Green">\$A\$1:\$A\$8,2</font>)=ROWS(<font color="Green">D\$1:D1</font>)</font>),COUNTIFS(<font color="Red">\$A\$11,"<="&\$A\$1:\$A\$8,\$B\$11,">="&\$A\$1:\$A\$8</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">01/04/2014</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Monday</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">14/04/2014</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Tuesday</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">01/05/2014</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Wednesday</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">27/05/2014</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Thursday</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">05/06/2014</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Friday</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">01/07/2014</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Saturday</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">16/08/2014</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Sunday</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">01/09/2014</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;">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></tr><tr ><td style="color: #161120;text-align: center;">10</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="font-weight: bold;text-align: right;;">15/05/2014</td><td style="font-weight: bold;text-align: right;;">16/07/2014</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 #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet3</p><br /><br />

missed the other criteria

Worksheet Formulas
CellFormula
E1=SUMPRODUCT(--(WEEKDAY(\$A\$1:\$A\$8,2)=ROWS(D\$1:D1)))

</tbody>

Excel 2013
ABCDE
101/04/2014Monday1
214/04/2014Tuesday3
301/05/2014Wednesday0
427/05/2014Thursday2
505/06/2014Friday0
601/07/2014Saturday1
716/08/2014Sunday1
807/09/2014

</tbody>
Sheet3

##### New Member

Thank you so much. Worked first time. Just what I needed.

##### New Member
How do I get it to adjust for a value error within that field. For example, what if someone put "NA" into the A column?

#### Weazel

##### Well-known Member
Ideally preventing users from entering anything but a date would be ideal, in lieu of that you can try something like this which is an array.

again there's probably a better way...

=SUM(IF(\$A\$2:\$A\$9<>"na",IF(TEXT(WEEKDAY(\$A\$2:\$A\$9),"dddd")=C2,IF(\$A\$2:\$A\$9>=A11,IF(\$A\$2:\$A\$9<=\$B\$11,1))))) control shift enter

Replies
9
Views
114
Replies
5
Views
106
Replies
0
Views
27
Replies
17
Views
130
Replies
2
Views
47