SUMPRODUCT

[Scott Kieta]

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

 

[John C]

http://www.microsoft.com/office/com...035a&mid=50409774-9f3b-4e81-bdda-8d398339035a

 

[Scott Kieta]

Thank you, i was not sure how to find my initial question, i will work off of
that.

 

[Sheeloo]

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]

That makes perfect sense however for some reason my formula continues to come
up with an answer of "0"

Thanks

 

[Sheeloo]

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