Week by week report

[thedeadzeds]

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

[Ken Puls]

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.

[thedeadzeds]

Thanks ken its actually excel 2003

[Ken Puls]

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]

works like a treat, thanks very much Ken

[Pecoflyer]

Cross post http://www.msofficeforums.com/excel/...ek-report.html