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

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

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



Look he

http://www.contextures.com/xlFunctions01.html#SumIf

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

Assuming B2:H2 shift range, and B3:H3 hour range, criteria in A10

In B10: =SUMIF($B$2:$H$2,$A10,$B$3:$H$3)

Adjust your range to suit



"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

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

Sorry, formula should be {=SUM((B2:H2=4)*(B3:H3))} as in your example and
change the "4" to suit the shift number.

"PJ" wrote:

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