Sum values per day

Albert 1 · Apr 24th, 2002, 04:16 PM

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

Mark W. · Apr 24th, 2002, 04:20 PM

{=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 · Apr 24th, 2002, 04:37 PM

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

Albert 1 · Apr 24th, 2002, 04:56 PM

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

Mark W. · Apr 24th, 2002, 04:59 PM

Quote:

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 · Apr 24th, 2002, 05:01 PM

Thank you very much again!

Mark W. · Apr 24th, 2002, 05:05 PM

Shall we go for the average Friday value...

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

Yogi Anand · Apr 24th, 2002, 05:11 PM

Quote:

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 · Apr 24th, 2002, 05:14 PM

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

Regards!

Yogi Anand

Mark W. · Apr 24th, 2002, 05:19 PM

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 24th, 2002, 04:16 PM	#1
Albert 1 Board Regular Join Date: Feb 2002 Posts: 390	A1:A100 contents dates, B1:B100 values. How can I sum the values of all Fridays?

Apr 24th, 2002, 04:20 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	{=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 ]

Apr 24th, 2002, 04:37 PM	#3
Albert 1 Board Regular Join Date: Feb 2002 Posts: 390	Works like a charm. Thank you very much for the quick response!

Apr 24th, 2002, 04:56 PM	#4
Albert 1 Board Regular Join Date: Feb 2002 Posts: 390	I have another question yet. How can I calculate how many Fridays there are in A1:A100?

Apr 24th, 2002, 05:01 PM	#6
Albert 1 Board Regular Join Date: Feb 2002 Posts: 390	Thank you very much again!

Apr 24th, 2002, 05:05 PM	#7
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Shall we go for the average Friday value... {=AVERAGE(IF(WEEKDAY(A1:A100)=6,B1:B100))}

Apr 24th, 2002, 05:14 PM	#9
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Sorry, Mark ... I did not see your post till after I posted mine. Yours is shorter and more elegant. Regards! Yogi Anand