sumproduct

[FPJ]

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?

[Bob Phillips]

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

--
HTH

Bob Phillips

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

"FPJ" <[email protected]> wrote in message
news:[email protected]...
> 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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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