Try this array formula, entered by pressing Ctrl Shift and Enter
simultaneously which puts the braces round the formula:

{=SUM((B10:H10=3)*(B11:H11))}

"Elton Law" wrote:

Dear Expert,
I have problem in doing a task.
Say below ...
A1 is name. B1 to Z1 is comprised of 25 names ...
A2 is Shifts. There are 12 Shifts in a week.
Even it works, they may have different working hours in a shift.
So, wanna calculate the total hours for shift 4 regarding who did it...
In this case, the answer is 3+4+2=9
For Shift 3, total hours are 2+4=6

Name Jimmy Fiona Cindy Crystal Peter Peggy John
Shifts 1 3 4 2 3 4 4
Hours 3 2 3 4 4 4 2

I cannot use subtotal nor sorting nor marco as order will be distorted.

If only functions are allowed, what can I do please? Hlookup does not help
as search "4", it often stops at Cindy column and the the rest does not work
...
Is there any first Hlookup and Second Hlookup feature?
Real spreadsheet is big as names are 25 ...
Shifts are 12 and not as simple as this one.
But your suggestion can help me to build into my model.

Thanks