countif with date range

M

MASHED_33

New Member
Joined
Nov 26, 2009
Messages
12
Hi,

I'm trying to count the Excellent and Very Good "Status" from range Jan. 27 to Feb. 3; I tried a simple function =SUMPRODUCT(--(AW27:AW45<"02/02/2011"+0),--(AB27:AB45="Very Good")) to just compute Very Good before Feb. 2, but it only returns zero.

I have just also tried counting Dates before Feb. 2 [=COUNTIFS(AW27:AW45,"<=$AW$30")], just to decipher what code should I use but also returns zero.

:banghead:


Status Date

Good 27/01/2011
Very Good 27/01/2011
Excellent 27/01/2011
Very Good 02/02/2011
Excellent 03/02/2011
Excellent 10/02/2011

Appreciate anyone's help on this. I'm using Office 2007
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Like this?

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 159px"><COL style="WIDTH: 71px"><COL style="WIDTH: 64px"><COL style="WIDTH: 71px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-FAMILY: Verdana">Good</TD><TD style="TEXT-ALIGN: right">27/01/2011</TD><TD> </TD><TD>S DATE</TD><TD>E DATE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Verdana">Very Good</TD><TD style="TEXT-ALIGN: right">27/01/2011</TD><TD> </TD><TD style="TEXT-ALIGN: right">27/01/2011</TD><TD style="TEXT-ALIGN: right">3/02/2011</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Verdana">Excellent</TD><TD style="TEXT-ALIGN: right">27/01/2011</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-FAMILY: Verdana">Very Good</TD><TD style="TEXT-ALIGN: right">2/02/2011</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-FAMILY: Verdana">Excellent</TD><TD style="TEXT-ALIGN: right">3/02/2011</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-FAMILY: Verdana">Excellent</TD><TD style="TEXT-ALIGN: right">10/02/2011</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">4</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A8</TD><TD>=SUMPRODUCT(--(B1:B6>=D2),--(B1:B6<=E2),--(ISNUMBER(MATCH(A1:A6,{"Very Good","Excellent"},0))))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 
Upvote 0
shemayisroel said:
Like this?

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 159px"><COL style="WIDTH: 71px"><COL style="WIDTH: 64px"><COL style="WIDTH: 71px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-FAMILY: Verdana">Good</TD><TD style="TEXT-ALIGN: right">27/01/2011</TD><TD></TD><TD>S DATE</TD><TD>E DATE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Verdana">Very Good</TD><TD style="TEXT-ALIGN: right">27/01/2011</TD><TD></TD><TD style="TEXT-ALIGN: right">27/01/2011</TD><TD style="TEXT-ALIGN: right">3/02/2011</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Verdana">Excellent</TD><TD style="TEXT-ALIGN: right">27/01/2011</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-FAMILY: Verdana">Very Good</TD><TD style="TEXT-ALIGN: right">2/02/2011</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-FAMILY: Verdana">Excellent</TD><TD style="TEXT-ALIGN: right">3/02/2011</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-FAMILY: Verdana">Excellent</TD><TD style="TEXT-ALIGN: right">10/02/2011</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">4</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A8</TD><TD>=SUMPRODUCT(--(B1:B6>=D2),--(B1:B6<=E2),--(ISNUMBER(MATCH(A1:A6,{"Very Good","Excellent"},0))))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Click to expand...

Thanks shemayisroel, I tried your formula; here's my version =SUMPRODUCT(--(AW27:AW45>=AW15),--(AW27:AW45<=AX15),--(ISNUMBER(MATCH(AB27:AB45,{"Very Good","Excellent"},0))))

but still returned 0 results. I'm not sure, if there are any settings in my excel that might be doing this.
 
Upvote 0
DCOUNT will give much better performance than SUMPRODUCT if your dataset is large.

Excel Workbook
ABCDE
1RatingDateDateRating
2GoodJanuary-27-11>=27/01/2011Very Good
3Very GoodJanuary-27-11Excellent
4ExcellentJanuary-27-11
5Very GoodFebruary-02-11
6ExcellentFebruary-03-11
7ExcellentFebruary-10-11
8
94
Sheet1
 
Upvote 0
delaneyjm said:
DCOUNT will give much better performance than SUMPRODUCT if your dataset is large.

Excel Workbook
ABCDE
1RatingDateDateRating
2GoodJanuary-27-11>=27/01/2011Very Good
3Very GoodJanuary-27-11Excellent
4ExcellentJanuary-27-11
5Very GoodFebruary-02-11
6ExcellentFebruary-03-11
7ExcellentFebruary-10-11
8
94
Sheet1
Click to expand...
Hi Delaneyjm, tried it as well but still returns 0.

=DCOUNT(B2:C21,H1,H1:I3) is the formula I've used
 
Upvote 0
It worked for me when I separated the very good count and the excellent count.

Code: 
=COUNTIFS(B3:B7,"Excellent",C3:C7,">="&E3,C3:C7,"<="&F3)+COUNTIFS(B3:B7,"Very Good",C3:C7,">="&E3,C3:C7,"<="&F3)

HTH
 
Upvote 0
Can you post a sample of your data? If the SUMPRODUCT formula provided by shemayisroel or the DCOUNT I provided are not working, they could be referencing different cells.

I included a link to Excel Jeanie which will allow you to take an html screenshot of your sheet.

If you aren't able to install the addin, put a border on all the cells in question, including the criteria cells if you are going to use the DCOUNT formula, and then paste it into a reply on the forum.
 
Upvote 0
MASHED_33 said:
Thanks shemayisroel, I tried your formula; here's my version =SUMPRODUCT(--(AW27:AW45>=AW15),--(AW27:AW45<=AX15),--(ISNUMBER(MATCH(AB27:AB45,{"Very Good","Excellent"},0))))

but still returned 0 results. I'm not sure, if there are any settings in my excel that might be doing this.
Click to expand...

Are your dates true dates?
 
Upvote 0
delaneyjm said:
Can you post a sample of your data? If the SUMPRODUCT formula provided by shemayisroel or the DCOUNT I provided are not working, they could be referencing different cells.

I included a link to Excel Jeanie which will allow you to take an html screenshot of your sheet.

If you aren't able to install the addin, put a border on all the cells in question, including the criteria cells if you are going to use the DCOUNT formula, and then paste it into a reply on the forum.
Click to expand...

Here it is delaneyjm

Excel Workbook
ABCDEFGHIJ
1Date of InterviewQ7: Overall quality of service received during this SR
2Q7: Overall quality of service received during this SRDate of Interview>=27/01/2011Very Good
3Very Good27/01/2011Excellent
4Very Good27/01/2011
5Poor27/01/2011027/01/2011
6Excellent02/02/201103/02/2011
7Very Good09/02/20110
8Very Good11/02/2011
9Good09/02/2011
10Good10/02/2011
11Very Good07/02/2011
12Very Good08/02/2011
13Excellent11/02/2011
14Excellent15/02/2011
15Excellent16/02/2011
16Excellent18/02/2011
17Good14/02/2011
18Very Good14/02/2011
19Excellent16/02/2011
20Excellent14/02/2011
21Excellent15/02/2011
22
Sheet2


E5 formula is =DCOUNT(B2:C21,H1,H1:I3)
E7 formula is =SUMPRODUCT(--(C3:C21>=H5),--(C3:C21<=H6),--(ISNUMBER(MATCH(B3:B21,{"Very Good","Excellent"},0))))


Thanks
 
Upvote 0
Hi,

To get the sum of Excellent and Very Good in dates >=H5 and <=H6 try this

=SUMPRODUCT(--((B3:B21="Very Good")+(B3:B21="Excellent")),--(C3:C21>=H5),--(C3:C21<=H6))

HTH

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Counting specific dates from multiple columns
Replies
2
Views
390
mattbird
M
JenniferMurphy
  • Question Question
Help with coin-collecting sheet
Replies
3
Views
160
felixstraube
F
D
  • Question Question
Countif / Sum including a date range
Replies
4
Views
242
djjamesp
D
B
  • Question Question
countifs and remove data completed
2
Replies
10
Views
917
MuchToLearn
M
AOB
  • Question Question
Calculate the "true" duration of up/down times accounting for gaps and overlaps
Replies
1
Views
716
AOB
AOB

Forum statistics

Threads
1,224,591
Messages
6,179,768
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