Sum Workdays

[Frank]

A lot of questions allready posted on this mater, but i can't seem to find
the right one.

I like to sum a range b2:b32 if the range a2:a32 is a workday.
A2:32 is a date which is a day of the month 1 to 31.

aka
day value
2010-1-1 1
2010-1-2 3
2010-1-3 6
2010-1-4 15
2010-1-5 2
etc.
What will be the formula to "sum column value only for workdays"?

Is this possible without creating a C-column with if-statement
(if(workday(a2;3)<5;b2;0))?

 

[Jacob Skaria]

Hi Frank

Try one of these

=SUMPRODUCT((WEEKDAY(A2:A32)={2,3,4,5,6})*(B2:B32))
=SUMPRODUCT((WEEKDAY(A2:A32)>1)*(WEEKDAY(A2:A32)<7),B2:B32)

 

[Mike H]

Hi,

If by 'workday' you mean 'weekday' i.e. Mon - Fri then try this

=SUMPRODUCT((WEEKDAY(A1:A14,2)<=5)*(B1:B14))

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Ron Rosenfeld]

A lot of questions allready posted on this mater, but i can't seem to find
the right one.

I like to sum a range b2:b32 if the range a2:a32 is a workday.
A2:32 is a date which is a day of the month 1 to 31.

aka
day value
2010-1-1 1
2010-1-2 3
2010-1-3 6
2010-1-4 15
2010-1-5 2
etc.
What will be the formula to "sum column value only for workdays"?

Is this possible without creating a C-column with if-statement
(if(workday(a2;3)<5;b2;0))?

Assuming that by workday you want the same definition as is used in the WORKDAY
worksheet function, then:

This formula must be **array-entered**:

=SUM((IF(ISNUMBER(dates),WORKDAY(dates-1,1,Holidays),-1)=dates)*values)

dates is your column of dates.
values is your column of values.
holidays is a range where you have entered the holiday dates
See HELP for the WORKDAY function for further information, especially if you
get a NAME error and are using a version of Excel prior to 2007
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.
--ron

 

[Frank]

first one didn't work second one did.
/!\ I had to replace "," into ";"

Below the working formula:
=SUMPRODUCT((WEEKDAY(A2:A32)>1)*(WEEKDAY(A2:A32)<7);B2:B32) (thanks to Jacob
Skaria)
alternative:
=SUMPRODUCT((WEEKDAY(A2:A32;3)<5)*1;(B2:B32)) (thanks to Mike H)

Dutch:
=SOMPRODUCT((WEEKDAG(A2:A32)>1)*(WEEKDAG(A2:A32)<7);B2:B32)
=SOMPRODUCT((WEEKDAG(A2:A32;3)<5)*1;(B2:B32))

 

[Ron Rosenfeld]

Assuming that by workday you want the same definition as is used in the WORKDAY
worksheet function, then:

This formula must be **array-entered**:

=SUM((IF(ISNUMBER(dates),WORKDAY(dates-1,1,Holidays),-1)=dates)*values)

dates is your column of dates.
values is your column of values.
holidays is a range where you have entered the holiday dates
See HELP for the WORKDAY function for further information, especially if you
get a NAME error and are using a version of Excel prior to 2007
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.
--ron

Just a quick note if you aren't aware: My US version uses the comma as a
separator. Your version apparently uses the semicolon as a separator. So the
formula should probably read:

=SUM((IF(ISNUMBER(dates);WORKDAY(dates-1;1;Holidays);-1)=dates)*values)



--ron