Sum of numbers in a cell that contains text and number dependant on Text Value?

[Boykickscar]

Hi all,

bit of a complex one, if you can help that would be fantastic...

so I am creating a calendar for employees that tracks holiday & absence (there will be other variables but for simplicity we'll keep it at that)

I need to firstly have excel identify the type of absence (illness or holiday) depending on the text identifier (H or A) then sum the value (which denotes number of hours that the absence lasts for)

here is a rough idea of how it would look... I would just need the holiday and absence columns to auto calculate.

example:

Day 1

Day 2

Day 3

Day 4

Day 5

Day 6

Day 7

Day 8

Day 9

Day 10

Holiday

Absence

Employee 1

H8

8

0

Employee 2

A4

H8

8

4

Employee 3

A7

0

7

Employee 4

H4

4

0

<tbody>
</tbody>

I'm using excel 2013.
please let me know if you need more info or if I have not explained it well enough.

many thanks in advance

 

[Scott T]

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Day1

Day2

Day3

Day4

Day5

Day6

Day7

Day8

Day9

Day10

Holiday

Absence

2

emp1

H8

8

0

3

emp2

A4

H8

8

4

4

emp3

A7

0

7

5

emp4

H4

4

0

6

<tbody>
</tbody>

In L2 and copy down

=SUM(IF(LEFT(B2:K2,1)="H",RIGHT(B2:K2,1),0)*1)

In M2 and copy down

=SUM(IF(LEFT(B2:K2,1)="A",RIGHT(B2:K2,1),0)*1)