Date Range for jobs & conversions SUMPRODUCT

**miribilist** · 11-03-2015, 05:21 AM

I'm trying to run a report within Excel that shows the total number of jobs completed by employees within a date range and also a count of the number of conversions per employee within the same date range.

My data is as follows:

Column A: Date (Range from A1:A100 spanning several years)
Column B: Employee Name (DAVE JOHN & BOB is the example employee name)
Column C: Converted (a simple binary showing 1 or 0 depending if the job has converted)
Cell D1: has the start date in my range (01/01/2015)
Cell D2: has the end date in my range (31/12/2015)
I have already worked out the basic count of jobs (in Column F)

=SUMPRODUCT((B1:B100=DAVE)*(A1:A100>=D1)*(A1:A100<=D2))
What I need to do is add to this to only count the number of records with this name within the date range but also have a "1" in column C to show the job has converted.

This will appear in Column G.

I have attached a screenshot of the example.

Capture.JPG

**Glenn Kennedy** · 11-03-2015, 05:29 AM

=SUMPRODUCT(($B$1:$B$100=$E4)*($C$1:$C$100=1)*($A$1:$A$100>=$D$1)*($A$1:$A$100<=$D$2))

If this isn't what you need, please attach an Excel sheet, not a picture of one which is really pretty much useless for testing.

**shukla.ankur281190** · 11-03-2015, 05:43 AM

Or
another way

F4=COUNTIFS($A$1:$A$100,">="&$D$1,$A$1:$A$100,"<="&$D$2,$B$1:$B$100,$E4,$C$1:$C$100,1)

Formula:

Please Login or Register  to view this content.

**miribilist** · 11-03-2015, 11:56 AM

Thanks everyone.
The solution that worked was:
=SUMPRODUCT(($B$1:$B$100=$E4)*($C$1:$C$100=1)*($A$1:$A$100>=$D$1)*($A$1:$A$100<=$D$2))

If I were to include another column that contains the total time for each job, how would I calculate an average time per job over all of these jobs per person?
Currently I have an all time average, but I need one to be calculated between the two date ranges.
=AVERAGEIF(B1:B100,C72,Z1:Z100) >>>C72 references a cell with the name and Column Z contains the time data in 00:00:00 format.

I have tried:
=AVERAGE((B3:B100=E4)*(Z3:Z100)*(A1:A100>=D1)*(A1:A100<=D2))

It's tricky to send the original excel file as it references over several sheets and contains sensitive data

**Glenn Kennedy** · 11-03-2015, 01:09 PM

It would really not take you long to mock up a sample of what you want. Very few people are going to want to re-type your data for you. However, you were fortunate, in that I had something that could be adaped in a few seconds.

Try this:

=AVERAGEIFS(D:D,A:A,">="&$F$1,A:A,"<="&$F$2,B:B,H2,C:C,1)

as shown in the attached sheet.

**Glenn Kennedy** · 11-03-2015, 01:11 PM

I have assumed that by "converted" you mean completed and that it is the completed jobs that you wish to have averaged. If this is not what you want, then modify the spreadsheet in the previous post to show what you DO want.