Summarize Weeks into Month

[seguin85]

Hi All,

I have some data by week set up similar to:

	5/22/17	5/29/17	6/5/17	6/12/17	6/19/17	6/26/17	7/3/17	7/10/17	7/17/17
Amount	1000	2000	2000	4000	4000	4000	4000	2000	2000

<tbody>
</tbody>

And I would like it to be summarized by month to:

	5/1/17	6/1/17	7/1/17
Amount	2000	4000	4000

<tbody>
</tbody>

So essentially it returns the max value that happens in that month and year. I am hoping to do this without using an array formula.

 

[63falcondude]

I'm not sure if this is possible using a non-array formula. Here is an array formula for you to consider:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1		5/22/2017	5/29/2017	6/5/2017	6/12/2017	6/19/2017	6/26/2017	7/3/2017	7/10/2017	7/17/2017
2	Amount	1000	2000	2000	4000	4000	4000	4000	2000	2000
3
4
5		5/1/2017	6/1/2017	7/1/2017
6	Amount	2000	4000	4000
Sheet1

Cell Formulas
Range	Formula
B6	{=MAX(IF(DATE(YEAR($B$1:$J$1),MONTH($B$1:$J$1),1)=B5,$B$2:$J$2))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Weazel]

could try doing it with the Aggregate function...

Unknown

	A	B	C	D	E	F	G	H	I	J
1		5/22/2017	5/29/2017	6/5/2017	6/12/2017	6/19/2017	6/26/2017	7/3/2017	7/10/2017	7/17/2017
2	Amount	1000	2000	2000	4000	4000	4000	4000	2000	2000
3
4		5/1/2017	6/1/2017	7/1/2017
5	Amount	2000	4000	4000

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B5 =AGGREGATE(14,6,$B$2:$J$2/(DATE(YEAR($B$1:$J$1),MONTH($B$1:$J$1),1)=B4),1) <tbody> </tbody>

<tbody>
</tbody>

 

[63falcondude]

could try doing it with the Aggregate function...

Unknown

	A	B	C	D	E	F	G	H	I	J
1		5/22/2017	5/29/2017	6/5/2017	6/12/2017	6/19/2017	6/26/2017	7/3/2017	7/10/2017	7/17/2017
2	Amount	1000	2000	2000	4000	4000	4000	4000	2000	2000
3
4		5/1/2017	6/1/2017	7/1/2017
5	Amount	2000	4000	4000

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B5 =AGGREGATE(14,6,$B$2:$J$2/(DATE(YEAR($B$1:$J$1),MONTH($B$1:$J$1),1)=B4),1) <tbody> </tbody>

<tbody>
</tbody>

Nice, I didn't think of that.

 

[seguin85]

I've never even heard of the aggregate function. How does it affect the performance? I have to use it in a bunch of areas throughout the file and don't want it to slow down too much.

 

[Weazel]

its more efficient than the MAX IF, how efficient I couldn't tell you, because it would depend on what else you have going on in your workbook.