=left(a1,14)
will return the leftmost 14 characters.

=left(a1,1)
will return the leftmost character.

If you don't put a number in that second argument, excel will know that you want
just 1 character. But I like being explicit.

FPJ wrote:

IT WORKS!!!!!!! Thank you very much!!!! I just have one more question: What
does the number 1 in (LEFT(B1:B100,1) stands for?

"Dave Peterson" wrote:

And you don't need the -- stuff:

=SUM((IF(ISNUMBER(A1:A100),MONTH(A1:A100))=1)*(LEF T(B1:B100,1)="1"))

Dave Peterson wrote:

=SUMPRODUCT(--(a1:a100<""),--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1"))
becomes
=SUM(--(IF(ISNUMBER(A1:A100),MONTH(A1:A100))=1)*(LEFT(B1: B100,1)="1"))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

FPJ wrote:

Yes, 2 of the cells in the range have the word VOID instead of the date. And
I want to keep the word VOID in there. How will the formula look like? Thanks.

"Dave Peterson" wrote:

Or maybe you have #value! errors in one of those ranges?

FPJ wrote:

I tried both formula and they are giving me a #VALUE result. Does it have
anything to do with the formula being linked to another worksheet?

"Dave Peterson" wrote:

Empty cells will be treated as being in January, too.

=SUMPRODUCT(--(a1:a100<""),--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1"))

is one way to avoid that problem.

Bob Phillips wrote:

=SUMPRODUCT(--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"FPJ" wrote in message
...
I have a question:

I have a worksheet that in one column contains the dates of the reports
were
generated and in another column 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 prefix 1 that occured on January?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson