Countif Weekday Array between multiple Dates

nomad737

New Member
Joined
Sep 24, 2014
Messages
3
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/201407/16/2014

<tbody>
</tbody>













MondayValue
TuesdayValue
WednesdayValue
ThursdayValue
FridayValue
SaturdayValue
SundayValue

<tbody>
</tbody>


Thank you for any assistance you can provide
 

Some videos you may like

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
Joined
Dec 24, 2011
Messages
3,155
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</SPAN>
Day</SPAN>
Count</SPAN>
2
4/1/2014 </SPAN>
Monday</SPAN>
0</SPAN>
3
4/14/2014 </SPAN>
Tuesday</SPAN>
2</SPAN>
4
5/1/2014 </SPAN>
Wednesday</SPAN>
0</SPAN>
5
5/27/2014 </SPAN>
Thursday</SPAN>
1</SPAN>
6
6/5/2014 </SPAN>
Friday</SPAN>
0</SPAN>
7
7/1/2014 </SPAN>
Saturday</SPAN>
0</SPAN>
8
8/16/2014 </SPAN>
Sunday</SPAN>
0</SPAN>
9
9/1/2014 </SPAN>
10
11
5/15/2014 </SPAN>
7/16/2014 </SPAN>

<TBODY>
</TBODY>
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
<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
Joined
Dec 16, 2013
Messages
2,857
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)))

<thead>
</thead><tbody>
</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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3
 

nomad737

New Member
Joined
Sep 24, 2014
Messages
3

ADVERTISEMENT

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

nomad737

New Member
Joined
Sep 24, 2014
Messages
3
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
Joined
Dec 24, 2011
Messages
3,155
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,203
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top