Hi,

Try this

=SUMPRODUCT((B2:H2=4)*(B3:H3))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Elton Law" wrote:

Typo is "So, wanna calculate the total hours for shift 4 regardless who did
it..."

Also, want to use a formula that can help me to work out the total hours
under certain shift.
In this case, if I want to calculate the shift 4, answer is 9.
If I want to calculate the shift 3, anwer is 6 ...
Thanks So much

"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