Time Sheet - Hlookup, Index, SumProduct or what?
 

In Col's C4:I4 have Mon thru Sun

In C5:I5 have store ID's i.e., "NL" and "JP" which can move to
different days from week to week.

Have hours for each day in C9:I9 related to store ID.

Need to Sum the hours for "NL" in J6 and Sum hours for "JP" in J9

Thanks for the help

Bob

Time Sheet - Hlookup, Index, SumProduct or what?
 

Try these:

=SUMIF(C5:I5,"NL",C9:I9)
=SUMIF(C5:I5,"JP",C9:I9)


--
Biff
Microsoft Excel MVP


"robert morris" wrote in message
...

In Col's C4:I4 have Mon thru Sun

In C5:I5 have store ID's i.e., "NL" and "JP" which can move to
different days from week to week.

Have hours for each day in C9:I9 related to store ID.

Need to Sum the hours for "NL" in J6 and Sum hours for "JP" in J9

Thanks for the help

Bob

Time Sheet - Hlookup, Index, SumProduct or what?
 

On 6 Jun., 04:14, robert morris
wrote:
In Col's C4:I4 have Mon thru Sun

In *C5:I5 *have store ID's *i.e., *"NL" *and "JP" which can move to
different days from week to week.

Have hours for each day in C9:I9 related to store ID. *

Need to Sum the hours for "NL" in J6 and Sum hours for "JP" in J9

Thanks for the help

Bob

Hi Bob

Try theese formlas in J6 and J9:

=SUMIF(C5:I5,"NL",C9:I9)
=SUMIF(C5:I5,"JP",C9:I9)

Regards,
Per

Time Sheet - Hlookup, Index, SumProduct or what?
 

If I understand your layout correctly, you can use these to get the hour
totals you want...

J6: =SUMPRODUCT((C5:I5="NL")*(C9:I9))

J9: =SUMPRODUCT((C5:I5="JP")*(C9:I9))

Rick


"robert morris" wrote in message
...

In Col's C4:I4 have Mon thru Sun

In C5:I5 have store ID's i.e., "NL" and "JP" which can move to
different days from week to week.

Have hours for each day in C9:I9 related to store ID.

Need to Sum the hours for "NL" in J6 and Sum hours for "JP" in J9

Thanks for the help

Bob

Time Sheet - Hlookup, Index, SumProduct or what?
 

SUMPRODUCT works well. =SUMPRODUCT(--(C5:I5="JP"),C9:I9) and
=SUMPRODUCT(--(C5:I5="NL"),C9:I9)

"robert morris" wrote:


In Col's C4:I4 have Mon thru Sun

In C5:I5 have store ID's i.e., "NL" and "JP" which can move to
different days from week to week.

Have hours for each day in C9:I9 related to store ID.

Need to Sum the hours for "NL" in J6 and Sum hours for "JP" in J9

Thanks for the help

Bob

Time Sheet - Hlookup, Index, SumProduct or what?
 

Hey fellows,

They all worked! Thanks to each and all of you for the help.

Bob

"T. Valko" wrote:

Try these:

=SUMIF(C5:I5,"NL",C9:I9)
=SUMIF(C5:I5,"JP",C9:I9)


--
Biff
Microsoft Excel MVP


"robert morris" wrote in message
...

In Col's C4:I4 have Mon thru Sun

In C5:I5 have store ID's i.e., "NL" and "JP" which can move to
different days from week to week.

Have hours for each day in C9:I9 related to store ID.

Need to Sum the hours for "NL" in J6 and Sum hours for "JP" in J9

Thanks for the help

Bob