Counting help, possible sumproduct / countif

[Joneye]

Cell B15 should have the answer 1 in it, as the booked date is in the past, what method can I use to count this?

<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Attendee</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">date</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Status</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">jon</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">09/08/2011</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Booked</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">james</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">jack</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">07/08/2011</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Completed</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">janice</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">sam</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">samantha</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">barry</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">09/08/2011</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Booked</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">ben</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">01/01/2011</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Booked</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">burt</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">07/08/2011</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Completed</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Answers</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Completed</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Booked</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Outstanding</td><td style="text-align: center;;"></td><td style="text-align: center;;"></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 />

 

[kpark91]

I dunno. You don't have any outstanding in your status.

 

[Joneye]

Cell C9 is as the date is in the past, The word "OUTSTANDING" is not there but as the box has not been completed I need to count this as outstanding.

 

[Jonmo1]

Perhaps

=COUNTIF(C2:C10,"")

 

[kpark91]

<?XML:NAMESPACE PREFIX = NOW())*(M2 /><NOW())*(M2:M10<><NOW())*(M2:M10<><NOW())*(M2:M10<>=SUMPRODUCT((M2:M10 < NOW()) * (M2:M10 <> "") * (N2:N10 <> "Completed"))</NOW())*(M2:M10<>
</NOW())*(M2:M10<>
</NOW())*(M2:M10<>

 

[kpark91]

I could not put the formula above without any spaces..

Any reasons why?

Btw, make sure you change M to B and N to C.

 

[Joneye]

I need to able to count the dates that are not in the future and not on the day, but in the past that have booked associated with them.

Is this possible I was thinking

sumproduct ?

 

[Aladin Akyurek]

Cell C9 is as the date is in the past, The word "OUTSTANDING" is not there but as the box has not been completed I need to count this as outstanding.

The question seems underspecified. Just guessing:

Does this...

=SUMPRODUCT(--ISNUMBER(C2:C10),--(C2:C10 < TODAY()),--(D2:D10="Booked"))

help?

 

[kpark91]

I need to able to count the dates that are not in the future and not on the day, but in the past that have booked associated with them.

Is this possible I was thinking

sumproduct ?

<?XML:NAMESPACE PREFIX = NOW())*(M2 /><NOW())*(M2:M10<><NOW())*(M2:M10<><NOW())*(M2:M10<>=SUMPRODUCT((B2:B10 < TODAY()) * (B2:B10 <> "") * (C2:C10 <> "Completed"))</NOW())*(M2:M10<></NOW())*(M2:M10<></NOW())*(M2:M10<>
<NOW())*(M2:M10<><NOW())*(M2:M10<><NOW())*(M2:M10<></NOW())*(M2:M10<></NOW())*(M2:M10<></NOW())*(M2:M10<>
<NOW())*(M2:M10<><NOW())*(M2:M10<><NOW())*(M2:M10<>ahhh Aladin has shown me that TODAY() is the actual answer xD</NOW())*(M2:M10<>
</NOW())*(M2:M10<>
</NOW())*(M2:M10<>

 

[Joneye]

<?XML:NAMESPACE PREFIX = NOW())*(M2 /><NOW())*(M2:M10<><NOW())*(M2:M10<><NOW())*(M2:M10<>=SUMPRODUCT((M2:M10 < NOW()) * (M2:M10 <> "") * (N2:N10 <> "Completed"))</NOW())*(M2:M10<>
</NOW())*(M2:M10<>
</NOW())*(M2:M10<>

Hi just seen your post - perfect... many thanks