# Multiple if and sum functions in excel 2003

Microsoft Office 2003 basic edition
March 17, 2010 at 05:49:43
Specs: Windows XP
 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.

See More: Multiple if and sum functions in excel 2003

#1
March 17, 2010 at 07:08:00
 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 \$ signsNow 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

Report •

#2
March 17, 2010 at 07:25:47
 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

Report •

#3
March 17, 2010 at 08:10:09
 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.

Report •

Related Solutions

#4
March 17, 2010 at 08:18:07
 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 whena) the condition is met e.g., flu andb) the number of days absent >5Regards

Report •

#5
March 26, 2010 at 08:58:34
 Still not working.Any other ideas

Report •

#6
March 26, 2010 at 09:10:26
 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

Report •

#7
March 26, 2010 at 10:02:48
 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!

Report •

#8
March 26, 2010 at 12:40:51
 Hi,Glad to hear its working.Thanks for the feedback.RegardsHumar

Report •