Sumproduct for holiday calendar

[thorpyuk]

Hiya,

Making an easy job complicated, i have a spreadsheet with colleague days off on holidays.

Down column A are my colleague names, and then across the next 5 columns are monday-friday. This is then replicated down rows for each week, so week 1 is directly under week 2 etc etc.

Now in a cell where someone is off on holiday is either "hol" for 1 days holiday, or "hol half day" for a half days holiday.

How might i use sumproduct to add up all a persons holiday days taken?

Thanks!

 

[DonkeyOte]

=SUMPRODUCT(($A$2:$A$13=$G3)*($B$2:$F$13="hol"))+(SUMPRODUCT(($A$2:$A$13=$G3)*($B$2:$F$13="hol half day"))/2)

my example is based on limited range row 2 to 13 and I had ee name in cell G3

 

[schielrn]

Can you not just use countif on the range for each employee?

=countif(B1:F100,"hol")+countif(B1:F100,"hol half day")*.5

Hope that helps. I assume the name is just once in column A with 52 rows for each week, but would need a little better description for the layour if this is not what you were looking for?