Sum values per day

[Albert 1]

A1:A100 contents dates, B1:B100 values.
How can I sum the values of all Fridays?

 

[Mark W.]

{=SUM((WEEKDAY(A1:A100)=6)*B1:B100)}

Note: This is an array formula which is entered using the Control+Shift+Enter key combination. For more on array formulas see the Excel Help Index topic for "About array formulas and how to enter them".
This message was edited by Mark W. on 2002-04-24 15:21

 

[Albert 1]

Works like a charm.
Thank you very much for the quick response!

 

[Albert 1]

I have another question yet.
How can I calculate how many Fridays there are in A1:A100?

 

[Mark W.]

On 2002-04-24 15:56, Albert 1 wrote:
I have another question yet.
How can I calculate how many Fridays there are in A1:A100?

{=SUM((WEEKDAY(A1:A100)=6)+0)}

 

[Albert 1]

Thank you very much again!

 

[Mark W.]

Shall we go for the average Friday value...

{=AVERAGE(IF(WEEKDAY(A1:A100)=6,B1:B100))}

 

[Yogi Anand]

On 2002-04-24 15:56, Albert 1 wrote:
I have another question yet.
How can I calculate how many Fridays there are in A1:A100?

Use the following array formula
=SUM(IF(WEEKDAY(A1:A100)=6,1,0))

HTH

 

[Yogi Anand]

Sorry, Mark ... I did not see your post till after I posted mine. Yours is shorter and more elegant.

Regards!

Yogi Anand

 

[Mark W.]

On 2002-04-24 16:14, Yogi Anand wrote:
Sorry, Mark ... I did not see your post till after I posted mine. Yours is shorter and more elegant.

Regards!

Yogi Anand

They're really about the same. I used a + operator instead of your IF function call. It's all about the same. You could shorten your IF argument list by omitting the 3rd argument...

{=SUM(IF(WEEKDAY(A1:A100)=6,1))}