Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula for sum
In a worksheet I have 30 or 31 days in a month marked ina row
For overtime I am using the following rule B for one rate, C for another and then writing the number of hours beside the letter for eg B2 stands for 2 hours OT B10 stands for 10 hours OT similarly C2 stands for 2 hours OT with C rate My intention is for excel to find out all the B's and ONLY sum the numbers(hours) beside them and give the result In a cell for the month Similarly for C rate find the Sum and give the result in another cell Hope you gurus can generate something to ease my troubles of manaul counting Afd |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula for sum
On Sat, 7 Nov 2009 21:45:01 +0300, "afdmello"
wrote: In a worksheet I have 30 or 31 days in a month marked ina row For overtime I am using the following rule B for one rate, C for another and then writing the number of hours beside the letter for eg B2 stands for 2 hours OT B10 stands for 10 hours OT similarly C2 stands for 2 hours OT with C rate My intention is for excel to find out all the B's and ONLY sum the numbers(hours) beside them and give the result In a cell for the month Similarly for C rate find the Sum and give the result in another cell Hope you gurus can generate something to ease my troubles of manaul counting Afd Assuming that there is never both B and C for the same day (cell), and that the data for a month is in cells A2:AE2, try the following formula for finding the total hour for B rate: =SUMPRODUCT(IF(ISNUMBER(--SUBSTITUTE("0"&A2:AE2,"B","")),--SUBSTITUTE("0"&A2:AE2,"B",""),0)) Note: This is an array formula and has to be confirmed bu CTRL+SHIFT+ENTER rather than just ENTER. Replace B with C to get the total hours for C rate. Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula for sum
Try these array formulas** :
For "B" overtime: =SUM(IF(LEFT(B2:B32)="B",--MID(B2:B32,2,2))) For "C" overtime: =SUM(IF(LEFT(B2:B32)="C",--MID(B2:B32,2,2))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "afdmello" wrote in message ... In a worksheet I have 30 or 31 days in a month marked ina row For overtime I am using the following rule B for one rate, C for another and then writing the number of hours beside the letter for eg B2 stands for 2 hours OT B10 stands for 10 hours OT similarly C2 stands for 2 hours OT with C rate My intention is for excel to find out all the B's and ONLY sum the numbers(hours) beside them and give the result In a cell for the month Similarly for C rate find the Sum and give the result in another cell Hope you gurus can generate something to ease my troubles of manaul counting Afd |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula for sum
It's magic to me.Bingo!!!
Thanks a million. Biff can you please tell me the significance of the two "--" in the formula. Please excuse my ignorance. The newsgroup has helped me always. I too want to learn as how you have done it Biff. Biff, We may never meet but remember that from across the miles your efforts are truly appreciated. As for Lars-Åke formula too gave me the right answer and thank you too Please accept my profound gratitude I am enamoured immensely by your skills and generous attitude to help. AFD "T. Valko" wrote in message ... Try these array formulas** : For "B" overtime: =SUM(IF(LEFT(B2:B32)="B",--MID(B2:B32,2,2))) For "C" overtime: =SUM(IF(LEFT(B2:B32)="C",--MID(B2:B32,2,2))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "afdmello" wrote in message ... In a worksheet I have 30 or 31 days in a month marked ina row For overtime I am using the following rule B for one rate, C for another and then writing the number of hours beside the letter for eg B2 stands for 2 hours OT B10 stands for 10 hours OT similarly C2 stands for 2 hours OT with C rate My intention is for excel to find out all the B's and ONLY sum the numbers(hours) beside them and give the result In a cell for the month Similarly for C rate find the Sum and give the result in another cell Hope you gurus can generate something to ease my troubles of manaul counting Afd |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula for sum
Double unary minus:
a.. http://www.mcgimpsey.com/excel/formulae/doubleneg.html b.. http://xldynamic.com/source/xld.SUMPRODUCT.html -- David Biddulph "afdmello" wrote in message ... It's magic to me.Bingo!!! Thanks a million. Biff can you please tell me the significance of the two "--" in the formula. Please excuse my ignorance. The newsgroup has helped me always. I too want to learn as how you have done it Biff. Biff, We may never meet but remember that from across the miles your efforts are truly appreciated. As for Lars-Åke formula too gave me the right answer and thank you too Please accept my profound gratitude I am enamoured immensely by your skills and generous attitude to help. AFD "T. Valko" wrote in message ... Try these array formulas** : For "B" overtime: =SUM(IF(LEFT(B2:B32)="B",--MID(B2:B32,2,2))) For "C" overtime: =SUM(IF(LEFT(B2:B32)="C",--MID(B2:B32,2,2))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "afdmello" wrote in message ... In a worksheet I have 30 or 31 days in a month marked ina row For overtime I am using the following rule B for one rate, C for another and then writing the number of hours beside the letter for eg B2 stands for 2 hours OT B10 stands for 10 hours OT similarly C2 stands for 2 hours OT with C rate My intention is for excel to find out all the B's and ONLY sum the numbers(hours) beside them and give the result In a cell for the month Similarly for C rate find the Sum and give the result in another cell Hope you gurus can generate something to ease my troubles of manaul counting Afd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|