Count Based On Date Range

K

kumara_faith

Well-known Member
Joined
Aug 19, 2006
Messages
922
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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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

C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
E
  • Question
Adding a custom message.
Replies
1
Views
417
Micron
M
M
  • Question
Best solution to consolidate values that occur on multiple dates/lines
Replies
5
Views
261
Peter_SSs
Peter_SSs
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
343
esben
E

Forum statistics

Threads
1,214,385
Messages
6,119,208
Members
448,874
Latest member
b1step2far

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