Count Based On Date Range

kumara_faith

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

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

fhqwgads

Board Regular
Joined
Jul 17, 2018
Messages
195
Office Version
  1. 2019
Platform
  1. Windows
like this?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-decoration: underline;;">Voucher Date</td><td style="font-weight: bold;text-decoration: underline;;">Loan</td><td style="text-align: right;;"></td><td style=";">Week 1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Tuesday, January 1, 2019</td><td style="text-align: right;;">15151515</td><td style="text-align: right;;"></td><td style="text-align: right;;">Tuesday, January 1, 2019</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Thursday, January 10, 2019</td><td style="text-align: right;;">15151516</td><td style="text-align: right;;"></td><td style="text-align: right;;">Wednesday, January 2, 2019</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Sunday, January 20, 2019</td><td style="text-align: right;;">15151517</td><td style="text-align: right;;"></td><td style="text-align: right;;">Thursday, January 3, 2019</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Friday, January 4, 2019</td><td style="text-align: right;;">15151518</td><td style="text-align: right;;"></td><td style="text-align: right;;">Friday, January 4, 2019</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Thursday, January 31, 2019</td><td style="text-align: right;;">15151519</td><td style="text-align: right;;"></td><td style="text-align: right;;">Saturday, January 5, 2019</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Sunday, January 6, 2019</td><td style="text-align: right;;">15151520</td><td style="text-align: right;;"></td><td style="text-align: right;;">Sunday, January 6, 2019</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">No</td><td style="text-align: right;;">3</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet33</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G9</th><td style="text-align:left">=COUNTIFS(<font color="Blue">C3:C8,">="&F3,C3:C8,"<="&F8</font>)</td></tr></tbody></table></td></tr></table><br />
 

kumara_faith

Well-known Member
Joined
Aug 19, 2006
Messages
618
Office Version
  1. 365
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 ?
 

fhqwgads

Board Regular
Joined
Jul 17, 2018
Messages
195
Office Version
  1. 2019
Platform
  1. Windows
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-decoration: underline;;">Voucher Date</td><td style="font-weight: bold;text-decoration: underline;;">Loan</td><td style="text-align: right;;"></td><td style=";">Week 1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Tuesday, January 1, 2019</td><td style="text-align: right;;">15151515</td><td style="text-align: right;;"></td><td style="text-align: right;;">Tuesday, January 1, 2019</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Thursday, January 10, 2019</td><td style="text-align: right;;">15151516</td><td style="text-align: right;;"></td><td style="text-align: right;;">Wednesday, January 2, 2019</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Sunday, January 20, 2019</td><td style="text-align: right;;">15151517</td><td style="text-align: right;;"></td><td style="text-align: right;;">Thursday, January 3, 2019</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Friday, January 4, 2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Friday, January 4, 2019</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Thursday, January 31, 2019</td><td style="text-align: right;;">15151519</td><td style="text-align: right;;"></td><td style="text-align: right;;">Saturday, January 5, 2019</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Sunday, January 6, 2019</td><td style="text-align: right;;">15151520</td><td style="text-align: right;;"></td><td style="text-align: right;;">Sunday, January 6, 2019</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">No</td><td style="text-align: right;;">2</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet33</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G9</th><td style="text-align:left">=COUNTIFS(<font color="Blue">C3:C8,">="&F3,C3:C8,"<="&F8,D3:D8,"<>"&""</font>)</td></tr></tbody></table></td></tr></table><br />

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?
 

kumara_faith

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

ADVERTISEMENT

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,"<>"&""
 

fhqwgads

Board Regular
Joined
Jul 17, 2018
Messages
195
Office Version
  1. 2019
Platform
  1. Windows
<> 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
 

majidsiddique

Board Regular
Joined
Oct 22, 2018
Messages
164

ADVERTISEMENT

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
 

kumara_faith

Well-known Member
Joined
Aug 19, 2006
Messages
618
Office Version
  1. 365
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,127,473
Messages
5,624,933
Members
416,064
Latest member
PaulBr2

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
Top