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")
$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
Your last range is E:AI which COUNTIFS can not handle.
Try to play with SUMPRODCT.
There are currently 1 users browsing this thread. (0 members and 1 guests)