CountIFs

L

lohith1984

New Member
Joined
Sep 23, 2009
Messages
12
Hello XL Experts,

I am in need of your assistance for my problem in excel, and i hope you can help me out.
Here is the scenario: I have 2 worksheets in a workbook one worksheet (Sheet1) has data corresponding to the date and the other worksheet (Sheet2) is where i need to extract no. of transactions made on particular date.
This can be done by using CountIFs or SumIFs function in 2007, but the problem is, in the sheet 1 the format of date is "04/19/10 04:00 am" and in the second sheet the format is "04/19/10", in this case the formula in sheet 2 cannot match with the date in sheet 1. Please advice.

Regards,
Lohith
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
lohith1984 said:
Hello XL Experts,

I am in need of your assistance for my problem in excel, and i hope you can help me out.
Here is the scenario: I have 2 worksheets in a workbook one worksheet (Sheet1) has data corresponding to the date and the other worksheet (Sheet2) is where i need to extract no. of transactions made on particular date.
This can be done by using CountIFs or SumIFs function in 2007, but the problem is, in the sheet 1 the format of date is "04/19/10 04:00 am" and in the second sheet the format is "04/19/10", in this case the formula in sheet 2 cannot match with the date in sheet 1. Please advice.

Regards,
Lohith
Click to expand...

Sheet2, B2:

=SUMPRODUCT((INT(Sheet1!$A$2:$A$100)=A2)+0)

where A2 on Sheet2 houses a date condition.
 
Upvote 0
Is seperating the date from the time feasible for you? Then have your formula look at the result of that
 
Upvote 0
Hi Aladin,

I tried using your formula, still i got the result "0".

Hi Scotty,

you mean extract the date from date and time to another column?
This is not allowed coz the format is fixed and no changes to made.

Regards,
Lohith
 
Upvote 0
lohith1984 said:
Hi Aladin,

I tried using your formula, still i got the result "0".
...
Click to expand...

Here is an example:

<TABLE style="WIDTH: 172pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=229 x:str><COLGROUP><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 76pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=101> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 width=64 align=right x:fmla="=TODAY()-2" x:num="40287">4/19/2010</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:fmla="=NOW()" x:num="40289.699024768517">4/21/2010 16:46</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:fmla="=SUMPRODUCT((INT($A$2:$A$10)=$C$1)+0)" x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="40290.175000000003">4/22/2010 4:12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="40291.216666666667">4/23/2010 5:12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="40287.216666666667">4/19/2010 5:12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="40287.216666666667">4/19/2010 5:12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="40287.216666666667">4/19/2010 5:12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="40317.175000000003">5/19/2010 4:12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="40317.091666666667">5/19/2010 2:12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="40287.133333333331">4/19/2010 3:12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR></TBODY></TABLE>

A2:A10 houses a sample od date/time data, C1 a date criterion, and...

C2:

=SUMPRODUCT((INT($A$2:$A$10)=$C$1)+0)

the desired count...
 
Upvote 0
Hi Aladin,

I need to use Countifs formula in my scenario, below is the example,

consider this is the data Sheet 1

<TABLE style="WIDTH: 122pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=162 border=0><COLGROUP><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 64pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" width=85 height=20>4/19/10 7:32 AM</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" width=77>Follow up MPO</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" height=20>4/19/10 7:32 AM</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" width=77>New</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" height=20>4/19/10 7:32 AM</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" width=77>New</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" height=20>4/19/10 7:32 AM</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" width=77>Follow up MPO</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" height=20>4/20/10 7:32 AM</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" width=77>New</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" height=20>4/20/10 7:32 AM</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" width=77>New</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" height=20>4/20/10 7:32 AM</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" width=77>Follow up MPO</TD></TR></TBODY></TABLE>

And in sheet 2
<TABLE style="WIDTH: 122pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=162 border=0><COLGROUP><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 64pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=85 height=20>19-Apr-10</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=77> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>20-Apr-10</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>21-Apr-10</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>22-Apr-10</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>

in sheet 2 i need to count no. of "Follow up MPO" and "New" on respective dates.
 
Upvote 0
lohith1984 said:
Hi Aladin,

I need to use Countifs formula in my scenario, below is the example,

consider this is the data Sheet 1

<TABLE style="WIDTH: 122pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=162><COLGROUP><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 64pt; FONT-FAMILY: Calibri; BACKGROUND: white; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl68 height=20 width=85>4/19/10 7:32 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; FONT-FAMILY: Calibri; BACKGROUND: white; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 width=77>Follow up MPO</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: white; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl68 height=20>4/19/10 7:32 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; FONT-FAMILY: Calibri; BACKGROUND: white; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl67 width=77>New</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: white; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl68 height=20>4/19/10 7:32 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; FONT-FAMILY: Calibri; BACKGROUND: white; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl67 width=77>New</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: white; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl68 height=20>4/19/10 7:32 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; FONT-FAMILY: Calibri; BACKGROUND: white; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 width=77>Follow up MPO</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: white; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl68 height=20>4/20/10 7:32 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; FONT-FAMILY: Calibri; BACKGROUND: white; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 width=77>New</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: white; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl68 height=20>4/20/10 7:32 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; FONT-FAMILY: Calibri; BACKGROUND: white; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl67 width=77>New</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: white; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl68 height=20>4/20/10 7:32 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; FONT-FAMILY: Calibri; BACKGROUND: white; COLOR: windowtext; FONT-SIZE: 8pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 width=77>Follow up MPO</TD></TR></TBODY></TABLE>

And in sheet 2
<TABLE style="WIDTH: 122pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=162><COLGROUP><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 64pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=85 align=right>19-Apr-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=77></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 align=right>20-Apr-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 align=right>21-Apr-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 align=right>22-Apr-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD></TR></TBODY></TABLE>

in sheet 2 i need to count no. of "Follow up MPO" and "New" on respective dates.
Click to expand...

Sheet2, B2
Code: 
=SUMPRODUCT(
   (INT(Sheet1!$A$2:$A$8)=$A2)+0,
   (Sheet1!$B$2:$B$8="Follow up MPO")+0)

If you want to count both "Follow up MPO" and "New" at the same time for each date...

Code: 
=SUMPRODUCT(
   (INT(Sheet1!$A$2:$A$8)=$A2)+0,
   ISNUMBER(MATCH(Sheet1!$B$2:$B$8,{"Follow up MPO","New"},0))+0)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Using IFS to calculate a task due by date but needing to remove weekends
Replies
3
Views
53
awoohaw
awoohaw
Noid67
  • Question
Multiple worksheet change and Formula use
2
Replies
17
Views
325
mumps
mumps
I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
207
Insurance Dave
I
M
  • Question
VBA hides/unhides columns based upon pivot table and value
Replies
0
Views
127
mapatton
M
A
  • Question
Creating a Calendar for a Month with tasks
Replies
0
Views
108
anuradhagrewal
A

Forum statistics

Threads
1,214,653
Messages
6,120,757
Members
448,991
Latest member
Hanakoro

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