Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with HLOOKUP/INDEX/MATCH | Excel Worksheet Functions | |||
vlookup, sumproduct, hlookup, index match, not sure | Excel Discussion (Misc queries) | |||
Replacing the row index in HLOOKUP | Excel Worksheet Functions | |||
Index / Hlookup | Excel Worksheet Functions | |||
INDEX,VLOOKUP HLOOKUP ? | Excel Worksheet Functions |