how much annual leave taken

[Rogerisit]

Hello!
I am trying to calculate how much annual leave "Bob" had during the week ending 6/1/2017. This sheet hasn't pasted so well, sorry. The "a" on the attendance sheet sits in A7. But I need to know for each entire week, and this formula needs to work for all employees. I'm stuck!! Thank you for any help

January 2017

Present

A

Annual Leave

S

Sick Leave

U

Unpaid Leave

W

Weekend worked

Sun 01

Mon 02

Tue 03

Wed 04

Thu 05

Fri 06

Sat 07

Sun 08

Mon 09

Tue 10

Wed 11

Thu 12

Fri 13

Sat 14

Sun 15

Mon 16

Tue 17

Wed 18

Thu 19

Fri 20

Sat 21

Sun 22

Mon 23

Tue 24

Wed 25

Thu 26

Fri 27

Sat 28

Sun 29

Mon 30

Tue 31

Bob

s

s

A

p

p

Jim

Frank

Arthur

Mary

Doris

Betty

Nancy

Charles

Annie

<colgroup><col span="2"><col span="31"></colgroup><tbody>
</tbody>

<colgroup><col span="2"><col span="31"></colgroup><tbody>
</tbody>

 

[Rogerisit]

And nor does "a/?"! Sumproduct isn't accepting this

 

[Eric W]

I don't know exactly what your workbook looks like now, but you seem to be able to adapt the formulas. So given the layout from post #2, try this:

B6: =SUMPRODUCT(--($G$5:$AK$5<=$E$2),--($G$5:$AK$5>=$E$2-6),--ISNUMBER(SEARCH(B$3,$G6:$AK6)))-SUMPRODUCT(--($G$5:$AK$5<=$E$2),--($G$5:$AK$5>=$E$2-6),--ISNUMBER(SEARCH(B$3,$G6:$AK6)),--ISNUMBER(SEARCH("/",$G6:$AK6)))/2

The first part, in red, works much the same way as the original formula. But instead of using --($G6:$AK6=B$3), I use the SEARCH function instead. This will now find and count a plain "a" as well as a "a/p" or "p/a". (SUMPRODUCT doesn't recognize wildcards.)

The next part, in green (how Christmassy!), does the same thing, but it counts those with an "a" AND a "/", then divides by 2 to show half a day, and subtracts from the first SUMPRODUCT. This works for a, p, and any of the other letters if you want.

Let me know if you get this to work.


Edit:

Here's a more succinct version, but it requires that you confirm it with Control+Shift+Enter when you put it in the formula bar:

=SUMPRODUCT(--($G$5:$AK$5<=$E$2),--($G$5:$AK$5>=$E$2-6),--ISNUMBER(SEARCH(B$3,$G6:$AK6)),IF(ISNUMBER(SEARCH("/",$G6:$AK6)),0.5,1))