Count Number of Times "Yes" appears between two dates

H

highflight1985

New Member
Joined
Jun 8, 2011
Messages
20
I'm trying to count the number of times "yes" appears in a column (N4:N563) between two dates (start date = A27, end date = A28, but I keep getting zero. I'm using the formula from this thread and trying to modify to work for me. But it's not...

Here's my formula:

=SUMPRODUCT(('Load Entry'!$N$4:$N$563="Yes")*('Load Entry'!$N$4:$N$563>=$A$28)*('Load Entry'!$N$4:$N$563>=$A$27))

Where am I going wrong?
 
Sort by date Sort by votes
highflight1985 said:
Okay, for clarity, on "Load Entry" I have this:

<TABLE style="WIDTH: 141pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=188 border=0><COLGROUP><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2073" width=65><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 1843" width=58><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2073" width=65><TBODY><TR style="HEIGHT: 13.8pt" height=18><TD class=xl74 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 49pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: #bfbfbf" width=65 height=18>C
​
</TD><TD class=xl74 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 43pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #bfbfbf" width=58>...
​
</TD><TD class=xl74 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 49pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #bfbfbf" width=65>N

</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl70 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: transparent" align=right height=18>1/1/10</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl72 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>Yes</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: transparent" align=right height=18>1/1/10</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>No</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: transparent" align=right height=18>1/6/10</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>Yes</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: transparent" align=right height=18>1/7/10</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>Yes</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: transparent" align=right height=18>1/8/10</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>No</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: transparent" align=right height=18>1/9/10</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>No</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: transparent" align=right height=18>1/11/10</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>No</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: transparent" align=right height=18>1/13/10</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>Yes</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: transparent" align=right height=18>1/16/10</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>Yes</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: transparent" align=right height=18>1/19/10</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>Yes</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: transparent" align=right height=18>1/20/10</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>Yes</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: transparent" align=right height=18>1/22/10</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>No</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: transparent" align=right height=18>1/23/10</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>No</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: transparent" align=right height=18>1/25/10</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>No</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: transparent" align=right height=18>1/26/10</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>No</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: transparent" align=right height=18>1/29/10</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>Yes</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: transparent" align=right height=18>2/3/10</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>No</TD></TR></TBODY></TABLE>​

Then, on the sheet where this formula is to count, I have this:

<TABLE style="WIDTH: 256pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=344 border=0><COLGROUP><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 2739" span=4 width=86><TBODY><TR style="HEIGHT: 13.8pt" height=18><TD class=xl70 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 64pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: #d9d9d9" align=right width=86 height=18></TD><TD class=xl70 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 64pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right width=86>A</TD><TD class=xl70 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 64pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right width=86>B</TD><TD class=xl70 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 64pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right width=86>C</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: #d9d9d9" align=right height=18>1</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right></TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right></TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right></TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: #d9d9d9" align=right height=18>...</TD><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right></TD><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right></TD><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right></TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: #d9d9d9" align=right height=18>9</TD><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right>Number of Matches:</TD><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right>(where the formula goes)</TD><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right></TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl72 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: #d9d9d9" align=right height=18>...</TD><TD class=xl72 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right></TD><TD class=xl72 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right></TD><TD class=xl72 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right></TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: #d9d9d9" align=right height=18>27</TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right>1/1/10</TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right></TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right></TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD class=xl74 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.8pt; BACKGROUND-COLOR: #d9d9d9" align=right height=18>28</TD><TD class=xl74 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right>1/8/10</TD><TD class=xl74 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right></TD><TD class=xl74 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #d9d9d9" align=right></TD></TR></TBODY></TABLE>

So, in cell B9 should be where the formula goes, using dates from A27 and A28, searching through dates on 'Load Entry''s C column, and matching any "Yes"s in "Load Entry''s N column....
Click to expand...
Ok, try this...

=SUMPRODUCT(--('Load Entry'!C4:C563>=A27),--('Load Entry'!C4:C563<=A28),--('Load Entry'!N4:N563="Yes"))
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
@ Aladin

Yes, after I had posted the above. I started posting it and then everyone else beat me. So...

I copied and pasted your formula and Excel gave me an error but autocorrected it. And now I get a number other than 0. It's giving me the correct number + 2?
 
Upvote 0
Okay, after some further testing, it turns out the above formula is not considering the dates for some reason... it's counting all of the times throughout the entire range that it see's "Yes" regardless of the dates.

EDIT: Disregard. I think I've found a workaround.
 
Last edited:
Upvote 0
highflight1985 said:
Okay, after some further testing, it turns out the above formula is not considering the dates for some reason... it's counting all of the times throughout the entire range that it see's "Yes" regardless of the dates.

EDIT: Disregard. I think I've found a workaround.
Click to expand...

Would you share the workaround?
 
Upvote 0
highflight1985 said:
Okay, after some further testing, it turns out the above formula is not considering the dates for some reason... it's counting all of the times throughout the entire range that it see's "Yes" regardless of the dates.

EDIT: Disregard. I think I've found a workaround.
Click to expand...
Can you post the formula?
 
Upvote 0
Kinda complicated to explain. Imagine 12 worksheets, one for each month. Now imagine your (Valko) formula used once per sheet in the same cell location with different date ranges appropriate to the month. Starting in January, the result of the formula was the summation of all the months before and decrementing as you get to December. By December, it showed only the one month's worth of results and the only correct value.

So, let's say the result of the formula should have been:

January: 4
February: 6
March: 3
April: 5
May: 2
June: 8
July: 3
August: 9
September: 1
October: 4
November: 2
December: 1

The results were actually showing:

January: 40
February: 36
March: 30
April: 27
May: 22
June: 28
July: 20
August: 17
September: 8
October: 7
November: 3
December: 1

Does that make sense?

I managed to work around it by using the formula a total of four times per sheet. On each month's sheet, I was already doing calculations on a weekly basis for other statistics, so I just broke down each month into its weeks. The formula would correctly sum on a weekly basis. So, I just did a quick and easy =Sum(Week1:Week4) to display a month's total...

I hope I didn't lose you guys. Lol
 
Upvote 0
highflight1985 said:
Kinda complicated to explain. Imagine 12 worksheets, one for each month. Now imagine your (Valko) formula used once per sheet in the same cell location with different date ranges appropriate to the month. Starting in January, the result of the formula was the summation of all the months before and decrementing as you get to December. By December, it showed only the one month's worth of results and the only correct value.

So, let's say the result of the formula should have been:

January: 4
February: 6
March: 3
April: 5
May: 2
June: 8
July: 3
August: 9
September: 1
October: 4
November: 2
December: 1

The results were actually showing:

January: 40
February: 36
March: 30
April: 27
May: 22
June: 28
July: 20
August: 17
September: 8
October: 7
November: 3
December: 1

Does that make sense?

I managed to work around it by using the formula a total of four times per sheet. On each month's sheet, I was already doing calculations on a weekly basis for other statistics, so I just broke down each month into its weeks. The formula would correctly sum on a weekly basis. So, I just did a quick and easy =Sum(Week1:Week4) to display a month's total...

I hope I didn't lose you guys. Lol
Click to expand...
Well, you lost me but that's not hard to do! :)

As long as you got it to work that's all that counts!

Thanks for the feedback! :cool:
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
count how many times text string appears in column
Replies
2
Views
222
ajm
A
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
260
Pingaware
P
S
  • Question Question
Sumproduct - to count occasions in a date range
Replies
3
Views
462
Cubist
Cubist
A
  • Question Question
Counting cells with specific text within two dates
Replies
2
Views
310
Cubist
Cubist
S
  • Question Question
How can I exactly count NETWORKDAYS in multiple date ranges, after a specific date?
Replies
1
Views
236
Tetra201
Tetra201

Forum statistics

Threads
1,224,612
Messages
6,179,890
Members
452,948
Latest member
Dupuhini

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