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))?

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)

--
Jacob


"Frank" wrote:

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))?

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))

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.


"Frank" wrote:

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))?