Week by week report

thedeadzeds

New member
Joined
Oct 25, 2011
Messages
37
Reaction score
0
Points
0
Excel Version(s)
2016
Hi All,

I have a spreadsheet which works great but I want to add some additional formulas and need a little help?

There are 2 tabs with this spreadsheet.

The first tab is raw data which shows name, date of audit and audit score. This data contains all audit scores for all members of staff .

The second tab actually shows an average audit score of the data week by week for each person.

Is there a way to add another column next to the percentage to show the number of audits completed? So this would be a count of the entries rather than an average?

So for example, column D would not show the amount of audits completed. Eg Derek Spenser between 30th Sept and 4th Oct = 4

I have attached another example.

Hope this makes sense?

Many thanks
Craig
 

Attachments

  • Copy of 0a1_WEEK BY WEEK_from forum.xls
    413 KB · Views: 26

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Answer actually depends on the version of Excel you're using. I think SUMIFS was added in 2007, but it might be 2010, so this may not work for you. If not, we'll reach to SUMPRODUCT instead.

Assumptions:
Start Date is in K2
End Date is in K3
Auditor is in K4

Formula: =COUNTIFS(B2:B2867,K4,C2:C2867,">="&K2,C2:C2867,"<="&K3)

Oh.. and there actually only appear to be 2 instances of Derek Spencer in that range. ;)
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Should have guessed by virtue of your example file being an XLS! :)

Try this formula instead. Same assumptions as above:

=SUMPRODUCT(--(B2:B2867=K4),--(C2:C2867>=K2),--(C2:C2867<=K3))
 

thedeadzeds

New member
Joined
Oct 25, 2011
Messages
37
Reaction score
0
Points
0
Excel Version(s)
2016
works like a treat, thanks very much Ken
 
Top