Counting records using date parts

K

Kris

I am trying to set up a formula based on a list of records that will count
how many items occur in a month based on a job function type. The job
function is in one column and the date is in the other.

For instance I have a list of jobs completed in January and February by
date. I want to know how many of Job A were in Jan and how many in Feb.

I have tried SUM,IF,AND and MONTH but I don't seem to be using the right
combination.
 
B

Bernard Liengme

=SUMPRODUCT(--(MONTH(A1:A100)=2))
will count how may of the dates in A1:A100 are in Feb
best wishes
 
K

Kris

Thanks, this is great but I need to put an additional step in that looks at
the job type. I have used COUNTIF(B1:B100=C7) Now i need to put them
together.
 
T

T. Valko

Try this:

=SUMPRODUCT(--(MONTH(A1:A100)=2),--(B1:B100=C7))

Note that an empty cell in your date range will be evaluated as month 1
(January). If you need to account for that:

=SUMPRODUCT(--(A1:A100<>""),--(MONTH(A1:A100)=1),--(B1:B100=C7))
 
Top