Hi there,
Arrggghh - this formula is making me lose hair - can anybody please help??
Along row 1, I have employee names, Bill, Ben, Ted etc and down collumn A, I have dates 1/1/11, 2/1/11, 3/1/11 etc. When an employee is off sick I want to place a letter 'S' in the correct cell which will show who and when an employee is off sick.
On a seperate sheet I have the names running down the collumn and two cells to select a date range which will tell me how many times that person has been off by reading off the big table
Currently I have this formula as an array
=COUNT(IF((HW!$A$2:$A$1108>=Comparision!$C$3)*(HW!$A$2:$A$1108<=Comparision!$D$3)*(B10=HW!D4),HW!D13 :D1108,"S"))
A13:A1108 are the dates
$C$3 is the from date range on the seperate sheet
$D$3 is where I want to enter the to date
B10 is the name looking up against the same name in HW!D4
At the moment it seems to be reading blank cells and not cells marked "S"
Does anybody know a fix???????????
Thanks
Pete
Could you post an example workbook, please?
Does this attachment work?
Last edited by FLANDERP; 01-31-2012 at 10:16 AM.
How about this:
=SUMPRODUCT(--(OFFSET(Absence!$B$2:$B$62,0,MATCH($B10,Absence!$B$1:$E$1,0)-1)="H"),--(Absence!$A$2:$A$62>=$C$3),--(Absence!$A$2:$A$62<=$D$3))
Edited to add: That's looking for "H", not "S", because none of the cells in your example were set to "S", but it will count "S" cells just as well with a minor change (marked in bold)
Last edited by Andrew-R; 01-31-2012 at 11:59 AM.
That works perfectly - Thanks alot!!!!!!!!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks