I have read a lot of the posts and a lot are close but not quite what i am
looking for.

Scenario: I have a spreadsheet to sum up total hours for the month as well
as count all the days of the month. The spreadsheet is set up for example
row1: dates 11-1 through 11-30 (alternating columns) dates are merged over 2
columns
row 2: Hours then OT (i.e. b2 is Hours, b3 is OT) and that continues along
row B to the Nov 30 date.
row 3: user name and the data for hours and OT per day (i.e. b2= 8, b3=2)
SUMPRODUCT(--($F$21:$BY$21=$F$21),--($F$22:$BY$220))) F21="Hours" and row
22 is the actual data. My problem is that if there is a Sat or sun worked
this calculation adds those days in as well as they are 0.

the other question has to do with the sum of those. How can i get 1 function
to only sum the weekdays and another function to sum the weekends?

I know it is a little confusing without pictures but any help would be
appreciated.

WHat i am trying to do is have a weekly summary of all hours where Mon- Fri
is Regular Hours and OT/ Sat/ Sun columns add up as OT. And at the same time
count the total days worked on a running total.

I have the running total by using networkdays function - the today( ) function

I know that with multiple conditions the sumproduct is usually used but i
seem to be having some issues, this is the formula i have currently for
counting.

Hi,

I'm running this is the vertical direction but the idea is the same.

=SUMPRODUCT(--(MOD(A1:A31,7)1),B1:B31)

Sums weekdays

=SUMPRODUCT(--(MOD(A1:A31,7)<2),B1:B31)

Sums weekends

--
Thanks,
Shane Devenshire


"Scott Kieta" wrote:

I have read a lot of the posts and a lot are close but not quite what i am
looking for.

Scenario: I have a spreadsheet to sum up total hours for the month as well
as count all the days of the month. The spreadsheet is set up for example
row1: dates 11-1 through 11-30 (alternating columns) dates are merged over 2
columns
row 2: Hours then OT (i.e. b2 is Hours, b3 is OT) and that continues along
row B to the Nov 30 date.
row 3: user name and the data for hours and OT per day (i.e. b2= 8, b3=2)
SUMPRODUCT(--($F$21:$BY$21=$F$21),--($F$22:$BY$220))) F21="Hours" and row
22 is the actual data. My problem is that if there is a Sat or sun worked
this calculation adds those days in as well as they are 0.

the other question has to do with the sum of those. How can i get 1 function
to only sum the weekdays and another function to sum the weekends?

I know it is a little confusing without pictures but any help would be
appreciated.

WHat i am trying to do is have a weekly summary of all hours where Mon- Fri
is Regular Hours and OT/ Sat/ Sun columns add up as OT. And at the same time
count the total days worked on a running total.

I have the running total by using networkdays function - the today( ) function

I know that with multiple conditions the sumproduct is usually used but i
seem to be having some issues, this is the formula i have currently for
counting.

Thanks, i tried it replacing range with my range but am receiving a #value!
answer. Your range provided A1:A31 is the dates of the month and B1:B31 are
the actual data, is this correct?

Thanks

"ShaneDevenshire" wrote:

Hi,

I'm running this is the vertical direction but the idea is the same.

=SUMPRODUCT(--(MOD(A1:A31,7)1),B1:B31)

Sums weekdays

=SUMPRODUCT(--(MOD(A1:A31,7)<2),B1:B31)

Sums weekends

--
Thanks,
Shane Devenshire


"Scott Kieta" wrote:

I have read a lot of the posts and a lot are close but not quite what i am
looking for.

Scenario: I have a spreadsheet to sum up total hours for the month as well
as count all the days of the month. The spreadsheet is set up for example
row1: dates 11-1 through 11-30 (alternating columns) dates are merged over 2
columns
row 2: Hours then OT (i.e. b2 is Hours, b3 is OT) and that continues along
row B to the Nov 30 date.
row 3: user name and the data for hours and OT per day (i.e. b2= 8, b3=2)
SUMPRODUCT(--($F$21:$BY$21=$F$21),--($F$22:$BY$220))) F21="Hours" and row
22 is the actual data. My problem is that if there is a Sat or sun worked
this calculation adds those days in as well as they are 0.

the other question has to do with the sum of those. How can i get 1 function
to only sum the weekdays and another function to sum the weekends?

I know it is a little confusing without pictures but any help would be
appreciated.

WHat i am trying to do is have a weekly summary of all hours where Mon- Fri
is Regular Hours and OT/ Sat/ Sun columns add up as OT. And at the same time
count the total days worked on a running total.

I have the running total by using networkdays function - the today( ) function

I know that with multiple conditions the sumproduct is usually used but i
seem to be having some issues, this is the formula i have currently for
counting.