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.

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.

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.

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

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

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

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

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

=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