Count Based On Date Range

K

kumara_faith

Well-known Member
Joined
Aug 19, 2006
Messages
955
Office Version
  1. 365
Hi,

I have a data input sheet as follows:

<!-- Please do not remove this header -->
<!-- Table easily created from Excel with ASAP Utilities (https://www.asap-utilities.com) -->
<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellpadding="0">
<tr><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><u><b>Voucher Date</b></u></font></td><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><u><b>Loan</b></u></font></td></tr>
<tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Tuesday, January 1, 2019</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">15151515</font></td></tr>
<tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Thursday, January 10, 2019</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">15151516</font></td></tr>
<tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Sunday, January 20, 2019</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">15151517</font></td></tr>
<tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Friday, January 4, 2019</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">15151518</font></td></tr>
<tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Thursday, January 31, 2019</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">15151519</font></td></tr>
<tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Sunday, January 6, 2019</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">15151520</font></td></tr>
</table>


I have created another table to identify the first week of Jan:

<!-- Please do not remove this header -->
<!-- Table easily created from Excel with ASAP Utilities (https://www.asap-utilities.com) -->
<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellpadding="0">
<tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Week 1</b></font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr>
<tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Tuesday, January 1, 2019</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr>
<tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Wednesday, January 2, 2019</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr>
<tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Thursday, January 3, 2019</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr>
<tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Friday, January 4, 2019</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr>
<tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Saturday, January 5, 2019</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr>
<tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Sunday, January 6, 2019</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr>
<tr><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>No</b></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">3</font></td></tr>
</table>

I am trying to summarize how many loan is recorded for the first week of Jan which is 3 based on the first table. I tried with the following formula but it is not working:

SUMPRODUCT(C3:C8)>=I3)*(C3:C8)<=I8),D3:D8))

Appreciate any help. Thank you.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
like this?


Book1
ABCDEFG
1
2Voucher DateLoanWeek 1
3Tuesday, January 1, 201915151515Tuesday, January 1, 2019
4Thursday, January 10, 201915151516Wednesday, January 2, 2019
5Sunday, January 20, 201915151517Thursday, January 3, 2019
6Friday, January 4, 201915151518Friday, January 4, 2019
7Thursday, January 31, 201915151519Saturday, January 5, 2019
8Sunday, January 6, 201915151520Sunday, January 6, 2019
9No3
Sheet33
Cell Formulas
RangeFormula
G9=COUNTIFS(C3:C8,">="&F3,C3:C8,"<="&F8)
 
Upvote 0
Hi,

Thank you.

Sometimes and very often there is a date but there is no loan recorded. As such, I would need to ensure that if the date of the first week is in column c, then the formula needs to ensure there is a loan recorded in column D, and then count the number of loan taken. This is to ensure there is a loan taken within the first week of Jan.

So , the formula needs to check for the dates in column C and then count the loan in column D. Is this possible ?
 
Upvote 0

Book1
ABCDEFG
1
2Voucher DateLoanWeek 1
3Tuesday, January 1, 201915151515Tuesday, January 1, 2019
4Thursday, January 10, 201915151516Wednesday, January 2, 2019
5Sunday, January 20, 201915151517Thursday, January 3, 2019
6Friday, January 4, 2019Friday, January 4, 2019
7Thursday, January 31, 201915151519Saturday, January 5, 2019
8Sunday, January 6, 201915151520Sunday, January 6, 2019
9No2
Sheet33
Cell Formulas
RangeFormula
G9=COUNTIFS(C3:C8,">="&F3,C3:C8,"<="&F8,D3:D8,"<>"&"")


here Friday, January 4, 2019 is within the first week but doesnt have an entry next to it and so isnt counted
is that what you wanted?
 
Upvote 0
Hi,

Yes, this is exactly what I was looking for. Thank you and appreciate your time and patience.

Could you explain what this means in the formula ?

D3:D8,"<>"&""
 
Upvote 0
<> is the symbol for does not equal to and the empty quotations "" denote a blank, so it's checking if cells in the range are not equal to blank
 
Upvote 0
Hi fhqwgads,
i am applying this formula, BUT get result 0, Why?
=COUNTIFS(A2:A7,">="&D2,A2:A7,"<="&D7)
=COUNTIFS(A2:A7,">="&D2,A2:A7,"<="&D7,B2:B7,"<>"&"")
this dates format not working formula
 
Upvote 0
Hi,

I also have ad additional third column that shows the Days the loan was taken. Is there a possibility to expand the formula to count any days that has exceeded 11 days ? Example of the table is as follows:

<!-- Please do not remove this header -->
<!-- Table easily created from Excel with ASAP Utilities (https://www.asap-utilities.com) -->
<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellpadding="0">
<tr><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Date</b></font></td><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Loan</b></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Days</b></font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b></b></font></td><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Week 1</b></font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr>
<tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">01-Dec-2018</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">15151515</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">12</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">01-Dec-2018</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr>
<tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">10-Dec-2018</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">15151515</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">12</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">02-Dec-2018</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr>
<tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">20-Dec-2018</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">15151515</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">11</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">03-Dec-2018</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr>
<tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">04-Dec-2018</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">10</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">04-Dec-2018</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr>
<tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">31-Dec-2018</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">15151515</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">9</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">05-Dec-2018</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr>
<tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">06-Dec-2018</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">15151515</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">9</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">06-Dec-2018</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr>
<tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>No</b></font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>1</b></font></td></tr>
</table>

Based on the above the result should be 1. Is this possible ? I tried modifying the formula as follows but it did not work:

=COUNTIFS(C3:C8,">="&G3,C3:C8,"<="&G8,D3:D8,"<>"&"")+COUNTIFS(E3:E8,">=11")
 
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
644
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
593
mumps
mumps
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
E
  • Question Question
Adding a custom message.
Replies
1
Views
671
Micron
M
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
90
Dazzybeeguy
D

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

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