Create a Subtotal for each week in the month using the =subtota(9,G2:G6500) formula

[Chris Waller]

Dear All,

I am using Excel 2010 and the spreadsheet I am working on contains 6,500 rows of data. I am currently using a filter to reduce the output to a certain product and I am using the =subtotal(9,C1:c6500) formula to create a subtotal, however, I would like to know if this formula or another formula can be modified so that I can it will create a subtotal for each week in the month?

TIA

 

[jorismoerings]

Care to share a post with some sample data, the conditions and the expected result?

 

[Chris Waller]

Sorry it has taken a while to get back, but I have been away at a family funeral. The attached list gives an idea of what I am trying to do. In a filtered list I am trying to create a subtotal for each of the weeks on the Spreadsheet. I have used a Sumif formula to create the weekly totals, but this does not work on a filtered list. I wonder if the subtotal formula linked with the product would actually allow me to create a subtotal for each week. I know I could apply the filters to more than one column, however, I was trying to be clever and only apply the filter on the product name and I was hoping that I could use a formula to create a subtotal for each week. Please see filtered list below. If there are any problems, please get back to me.

[TABLE="width: 287"]
<tbody>[TR]
[TD]Product Name[/TD]
[TD]Week[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]02-Oct-17[/TD]
[TD]16.5[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]02-Oct-17[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]02-Oct-17[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]02-Oct-17[/TD]
[TD]37.5[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]09-Oct-17[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]09-Oct-17[/TD]
[TD]7.5[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]09-Oct-17[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]09-Oct-17[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]09-Oct-17[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]16-Oct-17[/TD]
[TD]7.5[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]16-Oct-17[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]16-Oct-17[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]16-Oct-17[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]16-Oct-17[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]16-Oct-17[/TD]
[TD]1.25[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]23-Oct-17[/TD]
[TD]18.75[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]23-Oct-17[/TD]
[TD]7.5[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]23-Oct-17[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]23-Oct-17[/TD]
[TD]2.5[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]23-Oct-17[/TD]
[TD]2.5[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]23-Oct-17[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]23-Oct-17[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]23-Oct-17[/TD]
[TD]1.25[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]30-Oct-17[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]30-Oct-17[/TD]
[TD]7.5[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]30-Oct-17[/TD]
[TD]9.5[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]30-Oct-17[/TD]
[TD]1.25[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]30-Oct-17[/TD]
[TD]2.5[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]30-Oct-17[/TD]
[TD]37.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total for[/TD]
[TD]02-Oct-17[/TD]
[TD="align: right"]57.00[/TD]
[/TR]
[TR]
[TD]Total for[/TD]
[TD]09-Oct-17[/TD]
[TD="align: right"]54.00[/TD]
[/TR]
[TR]
[TD]Total for[/TD]
[TD]16-Oct-17[/TD]
[TD="align: right"]27.75[/TD]
[/TR]
[TR]
[TD]Total for[/TD]
[TD]23-Oct-17[/TD]
[TD="align: right"]50.50[/TD]
[/TR]
[TR]
[TD]Total for[/TD]
[TD]30-Oct-17[/TD]
[TD="align: right"]73.25[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[jorismoerings]

Hi,

No problem. Hope your doing well.
Take a look at this and see if this helps:

Book1
	A	B	C	D	E	F
1	Product Name	Week	Total		weeknum	Sum
2	Project 1	2-10-2017	16,5		40	57
3	Project 2	2-10-2017	2
4	Project 1	2-10-2017	1
5	Project 1	2-10-2017	37,5
6	Project 1	9-10-2017	30
7	Project 1	9-10-2017	7,5
8	Project 1	9-10-2017	14
9	Project 1	9-10-2017	1
10	Project 1	9-10-2017	1,5
11	Project 1	16-10-2017	7,5
12	Project 1	16-10-2017	13
13	Project 1	16-10-2017	1
14	Project 1	16-10-2017	3
15	Project 1	16-10-2017	2
16	Project 1	16-10-2017	1,25
17	Project 1	23-10-2017	18,8
18	Project 1	23-10-2017	7,5
19	Project 1	23-10-2017	11
20	Project 1	23-10-2017	2,5
21	Project 1	23-10-2017	2,5
22	Project 1	23-10-2017	4
Sheet1

Cell Formulas
Range	Formula
F2	=SUMPRODUCT(--(WEEKNUM(B2:B30+0)=40),SUBTOTAL(109,OFFSET(C2:C30,ROW(C2:C30)-MIN(ROW(C2:C30)),0,1)))