Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can anybody help me with basic sum formula please?
Hi Everyone, I am having really bad trouble with an excel spreadsheet made for one of our guys to monitor the sales in our company (selling phones). Basically we have a workbook that has all stuff like Pass, Fail, and Pendings etc for each sales person split up by days. Then we have formula at the end which adds up a summary of the weeks figures. The only thing,the guy puts in H (for holiday) or S (for sickness) in the cells if the sales person is absent, but this then screws up the formula and we cant see the totals! We need something that will add up the relevant cells but that counts a "H" or "S" as a "0" if it is present in the cell. The existing formula is just =SUM(E3+I3+M3+Q3+U3+Y3) obviously in this there is nothing to say count H or S as zero. Another person has suggested something like the formula below, however we can't get this to work either. =IF(OR(E3,I3,M3,Q3,U3,Y3="H"),0,IF(OR(E3,I3,M3,Q3, U3,Y3="S"),0,""))+(E3+I3+M3+Q3+U3+Y3) If anybody can help us out we would be eternally gratefull. I can't stress how much this would please us if somebody knew the answer or correct formula for this problem. I have uploaded the actual form itself if anyone wants to take a look at it. It is here http://s65.yousendit.com/d.aspx?id=1...M230PKIKYDLSK3 Thanks for taking your time to read this and i really appreciate any help. -Andy -- andrewsnaith ------------------------------------------------------------------------ andrewsnaith's Profile: http://www.excelforum.com/member.php...o&userid=32110 View this thread: http://www.excelforum.com/showthread...hreadid=518673 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can anybody help me with basic sum formula please?
hi andrew:
a klugy solution would be to use SUMIF for each day, ie =SUMIF(B3,"0",B3)+SUMIF(F3,"0",F3)+SUMIF(J3,"0" ,J3)+SUMIF(N3,"0",N3)+SUMIF(R3,"0",R3)+SUMIF(V3, "0",V3) hope that helps. cheers. chili. "andrewsnaith" wrote: Hi Everyone, I am having really bad trouble with an excel spreadsheet made for one of our guys to monitor the sales in our company (selling phones). Basically we have a workbook that has all stuff like Pass, Fail, and Pendings etc for each sales person split up by days. Then we have formula at the end which adds up a summary of the weeks figures. The only thing,the guy puts in H (for holiday) or S (for sickness) in the cells if the sales person is absent, but this then screws up the formula and we cant see the totals! We need something that will add up the relevant cells but that counts a "H" or "S" as a "0" if it is present in the cell. The existing formula is just =SUM(E3+I3+M3+Q3+U3+Y3) obviously in this there is nothing to say count H or S as zero. Another person has suggested something like the formula below, however we can't get this to work either. =IF(OR(E3,I3,M3,Q3,U3,Y3="H"),0,IF(OR(E3,I3,M3,Q3, U3,Y3="S"),0,""))+(E3+I3+M3+Q3+U3+Y3) If anybody can help us out we would be eternally gratefull. I can't stress how much this would please us if somebody knew the answer or correct formula for this problem. I have uploaded the actual form itself if anyone wants to take a look at it. It is here http://s65.yousendit.com/d.aspx?id=1...M230PKIKYDLSK3 Thanks for taking your time to read this and i really appreciate any help. -Andy -- andrewsnaith ------------------------------------------------------------------------ andrewsnaith's Profile: http://www.excelforum.com/member.php...o&userid=32110 View this thread: http://www.excelforum.com/showthread...hreadid=518673 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can anybody help me with basic sum formula please?
Try =SUBTOTAL(9,E3,I3,M3,Q3,U3,Y3) 9 refers to the SUM function and the next 6 arguments are the cells that you want to total. This function ignores text and just adds the numbers. -- mphell0 ------------------------------------------------------------------------ mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153 View this thread: http://www.excelforum.com/showthread...hreadid=518673 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can anybody help me with basic sum formula please?
Another way:
=SUM(IF((E30)*(I30)*(M30)*(Q30)*(U30)*(Y30), (E3,I3,M3,Q3,U3,Y3))) this is an array formula so enter it using Ctrl+Shift+Enter, if done correctly excel will put curly braces {} at each end of the formula. HTH Jean-Guy "andrewsnaith" wrote: Hi Everyone, I am having really bad trouble with an excel spreadsheet made for one of our guys to monitor the sales in our company (selling phones). Basically we have a workbook that has all stuff like Pass, Fail, and Pendings etc for each sales person split up by days. Then we have formula at the end which adds up a summary of the weeks figures. The only thing,the guy puts in H (for holiday) or S (for sickness) in the cells if the sales person is absent, but this then screws up the formula and we cant see the totals! We need something that will add up the relevant cells but that counts a "H" or "S" as a "0" if it is present in the cell. The existing formula is just =SUM(E3+I3+M3+Q3+U3+Y3) obviously in this there is nothing to say count H or S as zero. Another person has suggested something like the formula below, however we can't get this to work either. =IF(OR(E3,I3,M3,Q3,U3,Y3="H"),0,IF(OR(E3,I3,M3,Q3, U3,Y3="S"),0,""))+(E3+I3+M3+Q3+U3+Y3) If anybody can help us out we would be eternally gratefull. I can't stress how much this would please us if somebody knew the answer or correct formula for this problem. I have uploaded the actual form itself if anyone wants to take a look at it. It is here http://s65.yousendit.com/d.aspx?id=1...M230PKIKYDLSK3 Thanks for taking your time to read this and i really appreciate any help. -Andy -- andrewsnaith ------------------------------------------------------------------------ andrewsnaith's Profile: http://www.excelforum.com/member.php...o&userid=32110 View this thread: http://www.excelforum.com/showthread...hreadid=518673 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can anybody help me with basic sum formula please?
Thanks ever so much everyone it has really helped me out. I went for mphell's solutution and it seems to have worked, im ecstatic! I really am thankfull to all of you who helped me out on this one, i cant express how much of a pain this has been for me and im so glad to have got it sorted before the weekend! Once again many thanks, i really appreciate it. Cheers -Andy -- andrewsnaith ------------------------------------------------------------------------ andrewsnaith's Profile: http://www.excelforum.com/member.php...o&userid=32110 View this thread: http://www.excelforum.com/showthread...hreadid=518673 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help - basic formula question | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Dragging a Formula & Basic Setting Changes | Excel Discussion (Misc queries) | |||
Formula in Visual Basic For Excel | Excel Discussion (Misc queries) |