Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT
I tried submitting this before but i am not sure if it made it to the site, i
apologize if this is a duplicate. Scenario: Worksheet with month data Nov 1- Nov 30 (F21:BU21) Row 22 is the day format of those dates. Row 23 is alternating columns for attendance headers "Reg, "OT") all the way through BU23. G H 1-nov (Merged) SAT (Merged) Reg OT (sep columns) Trying to sum all "reg" hours to one summary column, and all "OT" hours to another column where any days Sat or Sun automatically sum to the OT hours. And do not sum with the Reg hours. This is my formula to start (which obvioulsy is not working) can someone help point me in the right direction? =SUMPRODUCT(F21:BU21=$F$21)*(F22:BW22=$G$22)*(--F23:BW24) $F$21 represents cell indicating "Sat", $g$22 represents "OT" I think i am overthinking it and just confusing myself |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT
http://www.microsoft.com/office/comm...a-8d398339035a
-- ** John C ** "Scott Kieta" wrote: I tried submitting this before but i am not sure if it made it to the site, i apologize if this is a duplicate. Scenario: Worksheet with month data Nov 1- Nov 30 (F21:BU21) Row 22 is the day format of those dates. Row 23 is alternating columns for attendance headers "Reg, "OT") all the way through BU23. G H 1-nov (Merged) SAT (Merged) Reg OT (sep columns) Trying to sum all "reg" hours to one summary column, and all "OT" hours to another column where any days Sat or Sun automatically sum to the OT hours. And do not sum with the Reg hours. This is my formula to start (which obvioulsy is not working) can someone help point me in the right direction? =SUMPRODUCT(F21:BU21=$F$21)*(F22:BW22=$G$22)*(--F23:BW24) $F$21 represents cell indicating "Sat", $g$22 represents "OT" I think i am overthinking it and just confusing myself |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT
Thank you, i was not sure how to find my initial question, i will work off of
that. "John C" wrote: http://www.microsoft.com/office/comm...a-8d398339035a -- ** John C ** "Scott Kieta" wrote: I tried submitting this before but i am not sure if it made it to the site, i apologize if this is a duplicate. Scenario: Worksheet with month data Nov 1- Nov 30 (F21:BU21) Row 22 is the day format of those dates. Row 23 is alternating columns for attendance headers "Reg, "OT") all the way through BU23. G H 1-nov (Merged) SAT (Merged) Reg OT (sep columns) Trying to sum all "reg" hours to one summary column, and all "OT" hours to another column where any days Sat or Sun automatically sum to the OT hours. And do not sum with the Reg hours. This is my formula to start (which obvioulsy is not working) can someone help point me in the right direction? =SUMPRODUCT(F21:BU21=$F$21)*(F22:BW22=$G$22)*(--F23:BW24) $F$21 represents cell indicating "Sat", $g$22 represents "OT" I think i am overthinking it and just confusing myself |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT
You can simplify the calculation if you enter all SAT/SUN hours in the OT
column. If you can do that then, =SUMPRODUCT(--(F22:BU22="Reg"),(F23:BW23)) will give you sum of Regular Hours =SUMPRODUCT(--(F22:BU22="OT"),(F23:BW23)) If not then =SUMPRODUCT(--(F21:BU21="SAT"),--(F22:BU22="Reg"),(F23:BU23)) will give you total of Sat Reg hours Similary for Sun =SUMPRODUCT(--(F21:BU21="SUN"),--(F22:BU22="Reg"),(F23:BU23)) Add these two and subtract from total reg hours "Scott Kieta" wrote: I tried submitting this before but i am not sure if it made it to the site, i apologize if this is a duplicate. Scenario: Worksheet with month data Nov 1- Nov 30 (F21:BU21) Row 22 is the day format of those dates. Row 23 is alternating columns for attendance headers "Reg, "OT") all the way through BU23. G H 1-nov (Merged) SAT (Merged) Reg OT (sep columns) Trying to sum all "reg" hours to one summary column, and all "OT" hours to another column where any days Sat or Sun automatically sum to the OT hours. And do not sum with the Reg hours. This is my formula to start (which obvioulsy is not working) can someone help point me in the right direction? =SUMPRODUCT(F21:BU21=$F$21)*(F22:BW22=$G$22)*(--F23:BW24) $F$21 represents cell indicating "Sat", $g$22 represents "OT" I think i am overthinking it and just confusing myself |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT
That makes perfect sense however for some reason my formula continues to come
up with an answer of "0" Thanks "Sheeloo" wrote: You can simplify the calculation if you enter all SAT/SUN hours in the OT column. If you can do that then, =SUMPRODUCT(--(F22:BU22="Reg"),(F23:BW23)) will give you sum of Regular Hours =SUMPRODUCT(--(F22:BU22="OT"),(F23:BW23)) If not then =SUMPRODUCT(--(F21:BU21="SAT"),--(F22:BU22="Reg"),(F23:BU23)) will give you total of Sat Reg hours Similary for Sun =SUMPRODUCT(--(F21:BU21="SUN"),--(F22:BU22="Reg"),(F23:BU23)) Add these two and subtract from total reg hours "Scott Kieta" wrote: I tried submitting this before but i am not sure if it made it to the site, i apologize if this is a duplicate. Scenario: Worksheet with month data Nov 1- Nov 30 (F21:BU21) Row 22 is the day format of those dates. Row 23 is alternating columns for attendance headers "Reg, "OT") all the way through BU23. G H 1-nov (Merged) SAT (Merged) Reg OT (sep columns) Trying to sum all "reg" hours to one summary column, and all "OT" hours to another column where any days Sat or Sun automatically sum to the OT hours. And do not sum with the Reg hours. This is my formula to start (which obvioulsy is not working) can someone help point me in the right direction? =SUMPRODUCT(F21:BU21=$F$21)*(F22:BW22=$G$22)*(--F23:BW24) $F$21 represents cell indicating "Sat", $g$22 represents "OT" I think i am overthinking it and just confusing myself |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT
Did you try my formulae?
Your formula =SUMPRODUCT(F21:BU21=$F$21)*(F22:BW22=$G$22)*(--F23:BW24) is trying to sum over two rows (23 & 24) I think you should chang the last range to F23:Bw24. It might also be giving you problems due to merging of cells in Row 21 "Scott Kieta" wrote: That makes perfect sense however for some reason my formula continues to come up with an answer of "0" Thanks "Sheeloo" wrote: You can simplify the calculation if you enter all SAT/SUN hours in the OT column. If you can do that then, =SUMPRODUCT(--(F22:BU22="Reg"),(F23:BW23)) will give you sum of Regular Hours =SUMPRODUCT(--(F22:BU22="OT"),(F23:BW23)) If not then =SUMPRODUCT(--(F21:BU21="SAT"),--(F22:BU22="Reg"),(F23:BU23)) will give you total of Sat Reg hours Similary for Sun =SUMPRODUCT(--(F21:BU21="SUN"),--(F22:BU22="Reg"),(F23:BU23)) Add these two and subtract from total reg hours "Scott Kieta" wrote: I tried submitting this before but i am not sure if it made it to the site, i apologize if this is a duplicate. Scenario: Worksheet with month data Nov 1- Nov 30 (F21:BU21) Row 22 is the day format of those dates. Row 23 is alternating columns for attendance headers "Reg, "OT") all the way through BU23. G H 1-nov (Merged) SAT (Merged) Reg OT (sep columns) Trying to sum all "reg" hours to one summary column, and all "OT" hours to another column where any days Sat or Sun automatically sum to the OT hours. And do not sum with the Reg hours. This is my formula to start (which obvioulsy is not working) can someone help point me in the right direction? =SUMPRODUCT(F21:BU21=$F$21)*(F22:BW22=$G$22)*(--F23:BW24) $F$21 represents cell indicating "Sat", $g$22 represents "OT" I think i am overthinking it and just confusing myself |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
How to use Sumproduct in VBA??? | Excel Discussion (Misc queries) | |||
SUMPRODUCT | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions |