-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Countifs problem
- Thread starter NadoDude
- Start date
mgirvin
Well-known Member
- Joined
- Dec 15, 2005
- Messages
- 1,236
- Office Version
- 365
- Platform
- Windows
With this clarification:
1) G column has whatever is in cell A7
and
2) K column has less than or equal to whatever is in cell M7
and
3) O column is blank or O column is greater than or equal to whatever is in cell L7
So if the record meets all four criteria, want a count of 1?
I think this will work:
=SUMPRODUCT(--(Assignments!$G$2:$G$2633=A7),--(Assignments!$K$2:$K$2633<=M7),(Assignments!$O$2:$O$2633="")+(Assignments!$O$2:$O$2633>=L7))
1) G column has whatever is in cell A7
and
2) K column has less than or equal to whatever is in cell M7
and
3) O column is blank or O column is greater than or equal to whatever is in cell L7
So if the record meets all four criteria, want a count of 1?
I think this will work:
=SUMPRODUCT(--(Assignments!$G$2:$G$2633=A7),--(Assignments!$K$2:$K$2633<=M7),(Assignments!$O$2:$O$2633="")+(Assignments!$O$2:$O$2633>=L7))
Upvote
0
Excel Facts
Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
barry houdini
MrExcel MVP
- Joined
- Mar 23, 2005
- Messages
- 20,825
Try like this
=SUMPRODUCT((Assignments!$G$2:$G$2633=A7)*(Assignments!$K$2:$K$2633<=M7)*((Assignments!$O$2:$O$2633>L7)+(Assignments!$O$2:$O$2633="")>0))
=SUMPRODUCT((Assignments!$G$2:$G$2633=A7)*(Assignments!$K$2:$K$2633<=M7)*((Assignments!$O$2:$O$2633>L7)+(Assignments!$O$2:$O$2633="")>0))
Upvote
0
NadoDude
Board Regular
- Joined
- Jun 24, 2010
- Messages
- 84
Here is the data from the assignments sheet. You can see that 1 person detached after 1/1/2009, and 9 personnel are still assigned
A7= North
L7 = 1/1/2009
M7 = 10/20/2010
<font size="2">Worksheet: Assignments UsedRange: $G$1:$O$11 Range: $G$1:$O$11</font><br /><br /><div style="border: 1px solid #666666; overflow: auto; width:580px; height: 220px;"><table border="1" cellspacing="2" cellpadding="3"><tr><td width="40px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2"> </font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">G</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">H</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">I</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">J</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">K</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">L</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">M</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">N</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">O</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG1_705"></a><font size="2">Division</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH1_705"></a><font size="2">Department</font></td><td width="100px" style="background-color:#ffffff;"><a name="cI1_705"></a><font size="2">Supervisor</font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ1_705"></a><font size="2">EDA</font></td><td width="100px" style="background-color:#ffffff;"><a name="cK1_705"></a><font size="2">Report Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL1_705"></a><font size="2">PRD</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM1_705"></a><font size="2">EDD</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN1_705"></a><font size="2">TERM LV Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO1_705"></a><font size="2">Detach Date</font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">2</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG2_705"></a><font size="2">North</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK2_705"></a><font size="2">11/18/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL2_705"></a><font size="2">10/31/2011</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cN2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO2_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">3</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG3_705"></a><font size="2">North</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK3_705"></a><font size="2">3/2/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL3_705"></a><font size="2">4/30/2012</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cN3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO3_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">4</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG4_705"></a><font size="2">North</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK4_705"></a><font size="2">8/1/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL4_705"></a><font size="2">1/1/2012</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cN4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO4_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">5</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG5_705"></a><font size="2">North</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK5_705"></a><font size="2">2/13/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL5_705"></a><font size="2">2/28/2011</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cN5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO5_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">6</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG6_705"></a><font size="2">North</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK6_705"></a><font size="2">8/1/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL6_705"></a><font size="2">2/28/2012</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cN6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO6_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">7</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG7_705"></a><font size="2">North</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH7_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI7_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ7_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK7_705"></a><font size="2">3/2/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL7_705"></a><font size="2">4/30/2012</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM7_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cN7_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO7_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">8</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG8_705"></a><font size="2">North</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH8_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI8_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ8_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK8_705"></a><font size="2">8/1/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL8_705"></a><font size="2">11/1/2011</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM8_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cN8_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO8_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">9</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG9_705"></a><font size="2">North</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH9_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI9_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ9_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK9_705"></a><font size="2">8/1/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL9_705"></a><font size="2">9/1/2011</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM9_705"></a><font size="2">8/31/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN9_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO9_705"></a><font size="2">8/31/2009</font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">10</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG10_705"></a><font size="2">North</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH10_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI10_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ10_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK10_705"></a><font size="2">8/1/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL10_705"></a><font size="2">5/1/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM10_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cN10_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO10_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">11</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG11_705"></a><font size="2">North</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH11_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI11_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ11_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK11_705"></a><font size="2">1/9/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL11_705"></a><font size="2">1/1/2011</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM11_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cN11_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO11_705"></a><font size="2"></font></td></table></div><p />
A7= North
L7 = 1/1/2009
M7 = 10/20/2010
<font size="2">Worksheet: Assignments UsedRange: $G$1:$O$11 Range: $G$1:$O$11</font><br /><br /><div style="border: 1px solid #666666; overflow: auto; width:580px; height: 220px;"><table border="1" cellspacing="2" cellpadding="3"><tr><td width="40px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2"> </font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">G</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">H</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">I</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">J</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">K</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">L</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">M</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">N</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">O</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG1_705"></a><font size="2">Division</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH1_705"></a><font size="2">Department</font></td><td width="100px" style="background-color:#ffffff;"><a name="cI1_705"></a><font size="2">Supervisor</font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ1_705"></a><font size="2">EDA</font></td><td width="100px" style="background-color:#ffffff;"><a name="cK1_705"></a><font size="2">Report Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL1_705"></a><font size="2">PRD</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM1_705"></a><font size="2">EDD</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN1_705"></a><font size="2">TERM LV Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO1_705"></a><font size="2">Detach Date</font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">2</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG2_705"></a><font size="2">North</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK2_705"></a><font size="2">11/18/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL2_705"></a><font size="2">10/31/2011</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cN2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO2_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">3</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG3_705"></a><font size="2">North</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK3_705"></a><font size="2">3/2/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL3_705"></a><font size="2">4/30/2012</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cN3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO3_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">4</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG4_705"></a><font size="2">North</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK4_705"></a><font size="2">8/1/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL4_705"></a><font size="2">1/1/2012</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cN4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO4_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">5</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG5_705"></a><font size="2">North</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK5_705"></a><font size="2">2/13/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL5_705"></a><font size="2">2/28/2011</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cN5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO5_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">6</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG6_705"></a><font size="2">North</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK6_705"></a><font size="2">8/1/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL6_705"></a><font size="2">2/28/2012</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cN6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO6_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">7</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG7_705"></a><font size="2">North</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH7_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI7_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ7_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK7_705"></a><font size="2">3/2/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL7_705"></a><font size="2">4/30/2012</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM7_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cN7_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO7_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">8</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG8_705"></a><font size="2">North</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH8_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI8_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ8_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK8_705"></a><font size="2">8/1/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL8_705"></a><font size="2">11/1/2011</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM8_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cN8_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO8_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">9</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG9_705"></a><font size="2">North</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH9_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI9_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ9_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK9_705"></a><font size="2">8/1/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL9_705"></a><font size="2">9/1/2011</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM9_705"></a><font size="2">8/31/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN9_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO9_705"></a><font size="2">8/31/2009</font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">10</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG10_705"></a><font size="2">North</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH10_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI10_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ10_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK10_705"></a><font size="2">8/1/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL10_705"></a><font size="2">5/1/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM10_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cN10_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO10_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">11</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG11_705"></a><font size="2">North</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH11_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI11_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ11_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK11_705"></a><font size="2">1/9/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL11_705"></a><font size="2">1/1/2011</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM11_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cN11_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO11_705"></a><font size="2"></font></td></table></div><p />
Upvote
0
mgirvin
Well-known Member
- Joined
- Dec 15, 2005
- Messages
- 1,236
- Office Version
- 365
- Platform
- Windows
Hey NadoDude,
In case you were curious, the reason that barry houdini’s formula worked and mine did not is because of the following.
barry houdini’s Formula 1:
=SUMPRODUCT((Assignments!$G$2:$G$2633=A7)*(Assignments!$K$2:$K$2633<=M7)*((Assignments!$O$2:$O$2633>L7)+(Assignments!$O$2:$O$2633="")>0))
mgirvin’s Formula 2:
=SUMPRODUCT(--(Assignments!$G$2:$G$2633=A7),--(Assignments!$K$2:$K$2633<=M7),(Assignments!$O$2:$O$2633="")+(Assignments!$O$2:$O$2633>=L7))
With Formula 2 I assumed that the OR criteria, =blank and >=L7, were mutually exclusive. Meaning, that for any one record if there was a count of 1 for the blank there would never also be a count of 1 for the >=L7. But my assumption was incorrect because when there is a blank, the date in L7 is compared to a zero (blank cell) to get this:
BlankCell>=L7 = 0>=SerialNumberDate = TRUE
For the =blank criteria you would get:
BlankCell=”” = TRUE
So when you Boolean add you get:
TRUE + TRUE = 2
This adds 1 extra count of 1. If you had an array like this:
{2,0,1,0}
Your count would be 3 instead of 2.
barry houdini solved this be taking the array and creating the logical test:
{2,0,1,0}>0
Which yields:
{TRUE,FALSE,TRUE,FALSE}
And would lead to a count of 2.
Here is a video that takes the idea “Count with 3 AND criteria and the 3rd AND citeria contains an OR criteria” and shows how to use it to add, sum and do conditional formatting:
http://www.youtube.com/watch?v=zx8Csa0KP-E
In case you were curious, the reason that barry houdini’s formula worked and mine did not is because of the following.
barry houdini’s Formula 1:
=SUMPRODUCT((Assignments!$G$2:$G$2633=A7)*(Assignments!$K$2:$K$2633<=M7)*((Assignments!$O$2:$O$2633>L7)+(Assignments!$O$2:$O$2633="")>0))
mgirvin’s Formula 2:
=SUMPRODUCT(--(Assignments!$G$2:$G$2633=A7),--(Assignments!$K$2:$K$2633<=M7),(Assignments!$O$2:$O$2633="")+(Assignments!$O$2:$O$2633>=L7))
With Formula 2 I assumed that the OR criteria, =blank and >=L7, were mutually exclusive. Meaning, that for any one record if there was a count of 1 for the blank there would never also be a count of 1 for the >=L7. But my assumption was incorrect because when there is a blank, the date in L7 is compared to a zero (blank cell) to get this:
BlankCell>=L7 = 0>=SerialNumberDate = TRUE
For the =blank criteria you would get:
BlankCell=”” = TRUE
So when you Boolean add you get:
TRUE + TRUE = 2
This adds 1 extra count of 1. If you had an array like this:
{2,0,1,0}
Your count would be 3 instead of 2.
barry houdini solved this be taking the array and creating the logical test:
{2,0,1,0}>0
Which yields:
{TRUE,FALSE,TRUE,FALSE}
And would lead to a count of 2.
Here is a video that takes the idea “Count with 3 AND criteria and the 3rd AND citeria contains an OR criteria” and shows how to use it to add, sum and do conditional formatting:
http://www.youtube.com/watch?v=zx8Csa0KP-E
Upvote
0
