Counting data between dates

M

Macorden

New Member
Joined
Jan 14, 2011
Messages
37
Ok, I have tried this on my own and the sum if array I am using appears to be counting unique numbers, which I don't want in this case. Here is a sample of the data:

<table class="MsoNormalTable" style="width: 304.5pt; border: 1pt solid windowtext;" border="1" cellpadding="0" cellspacing="0" width="406"> <tbody><tr style="height: 15pt;"> <td style="width: 48pt; border: 1pt solid windowtext; padding: 0in; height: 15pt;" width="64">
</td> <td style="width: 56pt; border: 1pt solid windowtext; padding: 0in; height: 15pt;" width="75"> [FONT=&quot]D[/FONT]
</td> <td style="width: 56pt; border: 1pt solid windowtext; padding: 0in; height: 15pt;" width="75"> [FONT=&quot]E[/FONT]
</td> <td style="width: 48pt; border: 1pt solid windowtext; padding: 0in; height: 15pt;" width="64"> [FONT=&quot]F[/FONT]
</td> <td style="width: 48pt; border: 1pt solid windowtext; padding: 0in; height: 15pt;" width="64"> [FONT=&quot]Q[/FONT]
</td> <td style="width: 48pt; border: 1pt solid windowtext; padding: 0in; height: 15pt;" width="64"> [FONT=&quot]T[/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]1[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]2[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;"> [FONT=&quot]Date Completed[/FONT]
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;"> [FONT=&quot]Due Date[/FONT]
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;"> [FONT=&quot]Asset Seqnum[/FONT]
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;"> [FONT=&quot]PM Passed[/FONT]
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;"> [FONT=&quot]Completed[/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]3[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]02/15/2011[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]03/16/2011[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]200692[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;"> [FONT=&quot]Passed[/FONT]
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]TRUE[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]4[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]02/15/2011[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]03/16/2011[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]200665[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;"> [FONT=&quot]Passed[/FONT]
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]TRUE[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]5[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]02/16/2011[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]01/20/2011[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]200513[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;"> [FONT=&quot]Passed[/FONT]
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]TRUE[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]6[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]02/16/2011[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]01/20/2011[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]200514[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;"> [FONT=&quot]Passed[/FONT]
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]TRUE[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]7[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]02/16/2011[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]01/20/2011[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]200515[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;"> [FONT=&quot]Passed[/FONT]
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]TRUE[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]8[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]02/16/2011[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]01/20/2011[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]200516[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;"> [FONT=&quot]Passed[/FONT]
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]TRUE[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]9[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]02/17/2011[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]09/16/2011[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]200728[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;"> [FONT=&quot]Failed[/FONT]
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]TRUE[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]10[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]06/14/2011[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]09/17/2011[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]200692[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;"> [FONT=&quot]Passed[/FONT]
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]TRUE[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]11[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]10/26/2010[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]200474[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]FALSE[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]12[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]10/26/2010[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]200475[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]FALSE[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]13[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]10/26/2010[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]200472[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]FALSE[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]14[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]10/26/2010[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]200473[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]FALSE[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]15[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]10/26/2010[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]200476[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]FALSE[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]16[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]11/24/2010[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]201019[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]FALSE[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]17[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]11/24/2010[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]201016[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]FALSE[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]18[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]11/24/2010[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]200500[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]FALSE[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]19[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]03/02/2012[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]11/24/2010[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]201022[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;"> [FONT=&quot]Passed[/FONT]
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]TRUE[/FONT]​
</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]20[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]01/02/2010[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]11/24/2010[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]201017[/FONT]​
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;"> [FONT=&quot]Failed[/FONT]
</td> <td style="border: 1pt solid windowtext; padding: 0in; height: 15pt;">
[FONT=&quot]TRUE[/FONT]​
</td> </tr> </tbody> </table>
I need to know the number of Assets Seqnum with date between 1/1/2011 (A1) and 12/31/2011 (A2) where Completed is TRUE and PM Passed is "Passed". If the asset is counted twice, that is ok. We are wanting to capture that information. I also intend to get a Failed count as well, but I assume I can replace the Passed with Failed.

The current code is only counting the data once and doesn't appear to be working:
Code: 
=SUM(IF((BF_PM_PASSED="Passed")*(BF_PM_COMPLETED=TRUE)*(BF_COMPLETED_DATE<=($J$3))*(BF_COMPLETED_DATE>=($H$3)),1,0)) +CSE

Thanks!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Perhaps

=SUMPRODUCT(--(BF_PM_PASSED="Passed"),--(BF_PM_COMPLETED=TRUE),--(BF_COMPLETED_DATE<=$J$3),--(BF_COMPLETED_DATE>=$H$3))
 
Upvote 0
They coerce an array of False/True to an array of 0/1 that SUMPRODUCT can process.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
L
  • Solved
Help Please
2 3
Replies
21
Views
521
Asbestos_Jen
Asbestos_Jen
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
105
flashgordie
F

Forum statistics

Threads
1,224,590
Messages
6,179,762
Members
452,940
Latest member
rootytrip

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top