COUNTIF formula with date range
 

I have a question:

I have a worksheet that in column B contains the dates of the reports when
they were created and in column D are text codes (example: 1-UCF, 1-DEL,
1-HOM,
1-PAS, 2-UCF, 2-DEL, 2-HOM, 2-PAS).

My question is, how do you write the formula if you want to count the number
of codes that have the 1-UCF aND 2-UCF that occured on January.

Thanks a lot for all your help.

COUNTIF formula with date range
 

Hi FPJ,

unfortunately a COUNTIF can only have one criteria and therefore you need to
use SUMPRODUCT instead.

Take a look on the internet for examples a good one is

http://www.contextures.com/xlFunctio...tml#SumProduct

HTH
KM

"FPJ" wrote:

I have a question:

I have a worksheet that in column B contains the dates of the reports when
they were created and in column D are text codes (example: 1-UCF, 1-DEL,
1-HOM,
1-PAS, 2-UCF, 2-DEL, 2-HOM, 2-PAS).

My question is, how do you write the formula if you want to count the number
of codes that have the 1-UCF aND 2-UCF that occured on January.

Thanks a lot for all your help.

COUNTIF formula with date range
 

Assuming that you want to count January for a specific year, e.g. 200
and if 1-UCF and 2-UCF are the only codes ending with UCF....

=SUMPRODUCT(--(RIGHT(D1:D100,3)="UCF"),--(TEXT(B1:B100,"mmm yy")="Ja
06")

--
daddylongleg
-----------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...fo&userid=3048
View this thread: http://www.excelforum.com/showthread.php?threadid=57429

COUNTIF formula with date range
 

OK, I tried the SUMPRODUCT but it did not give me the correct result. I got
zero occurence when I should have got 2 occurences.

"Kevin McCartney" wrote:

Hi FPJ,

unfortunately a COUNTIF can only have one criteria and therefore you need to
use SUMPRODUCT instead.

Take a look on the internet for examples a good one is

http://www.contextures.com/xlFunctio...tml#SumProduct

HTH
KM

"FPJ" wrote:

I have a question:

I have a worksheet that in column B contains the dates of the reports when
they were created and in column D are text codes (example: 1-UCF, 1-DEL,
1-HOM,
1-PAS, 2-UCF, 2-DEL, 2-HOM, 2-PAS).

My question is, how do you write the formula if you want to count the number
of codes that have the 1-UCF aND 2-UCF that occured on January.

Thanks a lot for all your help.

COUNTIF formula with date range
 

Which formula did you try

--
daddylongleg
-----------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...fo&userid=3048
View this thread: http://www.excelforum.com/showthread.php?threadid=57429

COUNTIF formula with date range
 

Here's the example of the matrix.

B D
--------------------------------
1/2/2006 1-UCF
1/4/2006 1-PAS
1/7/2006 1-UCF
1/7/2006 2-HOM
1/8/2006 1-PAS
1/10/2006 2-PAS
1/26/2006 2-DEL
2/3/2006 1-HOM
2/5/2006 1-PAS

Thanks.

"daddylonglegs" wrote:


Assuming that you want to count January for a specific year, e.g. 2006
and if 1-UCF and 2-UCF are the only codes ending with UCF....

=SUMPRODUCT(--(RIGHT(D1:D100,3)="UCF"),--(TEXT(B1:B100,"mmm yy")="Jan
06"))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=574293

COUNTIF formula with date range
 

OK, it works OK for me, perhaps you have additional charaters in th
data or the dates aren't being recognised as such.

What results do you get for

=SUMPRODUCT(--(RIGHT(D1:D100,3)="UCF))

and

=SUMPRODUCT(--(TEXT(B1:B100,"mmm yy")="Jan 06")

--
daddylongleg
-----------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...fo&userid=3048
View this thread: http://www.excelforum.com/showthread.php?threadid=57429

COUNTIF formula with date range
 

The first formula is OK but what if there were also 2-UCF that occured in
Feb., say.....Feb. 10 and Feb. 12 both had 1-UCF codes. And I want to count
the number of occurences of the codes that begin with #1 that were generated
in Jan. only.

Thanks.

"daddylonglegs" wrote:


OK, it works OK for me, perhaps you have additional charaters in the
data or the dates aren't being recognised as such.

What results do you get for

=SUMPRODUCT(--(RIGHT(D1:D100,3)="UCF))

and

=SUMPRODUCT(--(TEXT(B1:B100,"mmm yy")="Jan 06"))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=574293

COUNTIF formula with date range
 

=SUMPRODUCT(--(D1:D100="1-UCF"),--(TEXT(B1:B100,"mmm yy")="Jan
06"))

--
Regards,
Tom Ogilvy


"FPJ" wrote:

The first formula is OK but what if there were also 2-UCF that occured in
Feb., say.....Feb. 10 and Feb. 12 both had 1-UCF codes. And I want to count
the number of occurences of the codes that begin with #1 that were generated
in Jan. only.

Thanks.

"daddylonglegs" wrote:


OK, it works OK for me, perhaps you have additional charaters in the
data or the dates aren't being recognised as such.

What results do you get for

=SUMPRODUCT(--(RIGHT(D1:D100,3)="UCF))

and

=SUMPRODUCT(--(TEXT(B1:B100,"mmm yy")="Jan 06"))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=574293

COUNTIF formula with date range
 

That works but I also want to count the other codes that start with 1 (i.e.
1-PAS, 1-DEL, 1-HOM) that occured in January. Thanks.

"Tom Ogilvy" wrote:

=SUMPRODUCT(--(D1:D100="1-UCF"),--(TEXT(B1:B100,"mmm yy")="Jan
06"))

--
Regards,
Tom Ogilvy


"FPJ" wrote:

The first formula is OK but what if there were also 2-UCF that occured in
Feb., say.....Feb. 10 and Feb. 12 both had 1-UCF codes. And I want to count
the number of occurences of the codes that begin with #1 that were generated
in Jan. only.

Thanks.

"daddylonglegs" wrote:


OK, it works OK for me, perhaps you have additional charaters in the
data or the dates aren't being recognised as such.

What results do you get for

=SUMPRODUCT(--(RIGHT(D1:D100,3)="UCF))

and

=SUMPRODUCT(--(TEXT(B1:B100,"mmm yy")="Jan 06"))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=574293

COUNTIF formula with date range
 

You have another thread active. Was that sufficient?

FPJ wrote:

That works but I also want to count the other codes that start with 1 (i.e.
1-PAS, 1-DEL, 1-HOM) that occured in January. Thanks.

"Tom Ogilvy" wrote:

=SUMPRODUCT(--(D1:D100="1-UCF"),--(TEXT(B1:B100,"mmm yy")="Jan
06"))

--
Regards,
Tom Ogilvy


"FPJ" wrote:

The first formula is OK but what if there were also 2-UCF that occured in
Feb., say.....Feb. 10 and Feb. 12 both had 1-UCF codes. And I want to count
the number of occurences of the codes that begin with #1 that were generated
in Jan. only.

Thanks.

"daddylonglegs" wrote:


OK, it works OK for me, perhaps you have additional charaters in the
data or the dates aren't being recognised as such.

What results do you get for

=SUMPRODUCT(--(RIGHT(D1:D100,3)="UCF))

and

=SUMPRODUCT(--(TEXT(B1:B100,"mmm yy")="Jan 06"))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=574293



--

Dave Peterson