Good Afternoon, Gurus,
In WORKSHEET 1, I have a large table:
Column A: Member Join Date
Column B: Member Name
Column C: Team Position
Column D: Team
Columns E - NE: Dates from 01/01/2013 to 31/12/13
There are 150+ rows listing those member details. The field between E2 and NE151 is intended to score the number of times one of those members submitted an inspection on a given date.
On WORKSHEET 2, I need a formula that tallies up the amount of inspections a team has conducted over the course of a month, noting that any inspections conducted by a person on date are not counted if their Member Join Date is after the reporting period. Columns A-D are so far sorted by Column B, but as this is intended to be a live document, it is not intended for these columns to be resorted into any other given order. I would prefer not to use VBA or PivotTables if at all possible.
So far, I've tried using the following formula, but I get a #VALUE! error.
=COUNTIFS('WORKSHEET 1'!$A:$A,">="&$B$2,'WORKSHEET 1!$A:$A,"<="&$C$2,'WORKSHEET 1'!$D:$D,$A6,'WORKSHEET 1'!$E:$AI,">0")
Where:
$B$2 = Beginning date of month
$C$2 = End date of month
$A$6 = Team being referenced
The formula does work correctly as:
=COUNTIFS('WORKSHEET 1'!$A:$A,">="&$B$2,'WORKSHEET 1!$A:$A,"<="&$C$2,'WORKSHEET 1'!$D:$D,$A6)
So I believe that the problems arise when I am trying to find out the total of inspections conducted.
I'm probably way freaking off with how I'm going about this, and look to your guidance for help