I have a spreadsheet which records staff absence. Each row has a different incident of absence and the relevant details relating to it. Column Q for each record shows the number of days lost and column S shows the type of illness (i.e. headaches or flu. I want a function for another sheet that will sum all of the cells in column Q that meet the criteria of, Q=0.5 and S=headaches. I will then use this for other criteria.
Any ideas.
Hi, If on Sheet2 you have a list of the conditions in column A, then you can get the number of days for each condition in column B:
A B 1 Condition Total days 2 Headache 8 3 Flu 32 4 Injury 16 5 Hospital 25
In cell B2 enter this formula:=SUMIF(Sheet1!$S$2:$S$14,A2,Sheet1!$Q$2:$Q$14)
Note the $ signs
Now drag the formula down alongside the conditions in column A.The $ signs are required to ensure that the formula continues to refer to the same range of cells on sheet 1.
Change this range to match the range used in columns Q and S on Sheet1 (the ranges must be the same number of rows).
The range can include empty cells, so that there is some space for adding additional staff, or you could use named ranges to make changes to the ranges used on Sheet1 easier to manage.
If your source data is in a worksheet with a name containing spaces then the name must be enclosed in single quotes like this:
=SUMIF('Source Data'!$S$2:$S$14,A2,'Source Data'!$Q$2:$Q$14)Regards
It doesnt quite work like that as the number of days off in column Q is different for each person which is where I have the problem. The value in column Q is entered manually by me for each person when they report they are sick and how long they have been off for. Then I also choose their illness from a drop down menu. I need the formula to sum cells in column Q that meet 2 criteria. They must >5 and the text in column R must read headache.
This formula is used to caluclate how many days were lost to headaches when the person was off for more than 5 days.
Any ideas
I'm going totally off-topic from an Excel perspective, but I have a question. Are you allowed to ask and keep records of why people are out sick? Isn't that getting into HIPA areas?
Just curious, that's all.
Hi, With the data in the same ranges as before, on Sheet1,
and with the conditions in column A on Sheet2, starting at cell A2,
put this formula in Sheet2 cell B2:=SUMPRODUCT((Sheet1!$S$2:$S$14=A2)*(Sheet1!$R$2:$R$14>5)*(Sheet1!$R$2:$R$14))This formula will only sum the days on each row in column R when
a) the condition is met e.g., flu and
b) the number of days absent >5Regards
Still not working. Any other ideas
Hi, Still not working, really doesn't help. What happens - is there no result, is there an error.
Can you confirm the columns containing the days and the column containing the conditions.Try clicking inside the formula and looking at the ranges that are outlined - do they match the ranges of days and conditions.
Regards
No problems now. I forgot I changed the columns around so needed to change the R to a Q in the formula. Thanks for your help!
Ad Choices