Count Entries Per Month

[KevinGrogan]

I'll first list the data I have an then list what I'm trying to do.

Columns:
A: Customer Name
B: Customer Number
C: Date Job was Submitted
D: Date Job was Returned
E: Turnaround Time
F: Associate who Completed the Job
G: Revenue from Job

The data I'm trying to generate is a table that will tell me on a
monthly basis how many jobs were submitted per month, average
turnaround time per month, how many each associate completed per
month, and total revenue per month.

Thanks in advance for your help.

 

[Guest]

You want something like this

=SUMPRODUCT(--(YEAR($C$1:$C$100)=2007),--(MONTH($C$1:$C$100)=6))

for June 2007 submissions.

For the # of jobs the associate completed, let's assume that the Associate
name is in X1 and that you count the month as when the job is completed. The
month and year you want to check is in Y1

=SUMPRODUCT(--(YEAR($C$1:$C$100)=YEAR($Y$1)),--(MONTH($C$1:$C$100)=MONTH($Y$1)),--($F$1:$F$100=$X$1 ))

HTH,
Barb Reinhardt

 

[KevinGrogan]

I tried that and I get a #VALUE! error. The dates are formatted using
the DATE function. Does that make a difference? The actual value in
the cell for January 2, 2007 is 39084.

 

[Peo Sjoblom]

You probably have a text entry in any of the ranges or in the criteria date
(Y1)

Select them and press F5, special and select text

 

[KevinGrogan]

I tried that and I get a #VALUE! error. The dates are formatted using
the DATE function. Does that make a difference? The actual value in
the cell for January 2, 2007 is 39084.

Nevermind, I got it to work. Some of my dates were formatted
incorrectly. Thanks so much.

 

[KevinGrogan]

How can I set it up to calculate the total revenue per month and the
average turnaround per month?

Thanks.