formula for sum

[afdmello]

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

 

[Lars-Åke Aspelin]

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

 

[T. Valko]

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.

 

[afdmello]

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

 

[David Biddulph]

Double unary minus:
a.. http://www.mcgimpsey.com/excel/formulae/doubleneg.html
b.. http://xldynamic.com/source/xld.SUMPRODUCT.html