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.

Product Name	Week	Total
Project 1	02-Oct-17	16.5
Project 1	02-Oct-17	2
Project 1	02-Oct-17	1
Project 1	02-Oct-17	37.5
Project 1	09-Oct-17	30
Project 1	09-Oct-17	7.5
Project 1	09-Oct-17	14
Project 1	09-Oct-17	1
Project 1	09-Oct-17	1.5
Project 1	16-Oct-17	7.5
Project 1	16-Oct-17	13
Project 1	16-Oct-17	1
Project 1	16-Oct-17	3
Project 1	16-Oct-17	2
Project 1	16-Oct-17	1.25
Project 1	23-Oct-17	18.75
Project 1	23-Oct-17	7.5
Project 1	23-Oct-17	11
Project 1	23-Oct-17	2.5
Project 1	23-Oct-17	2.5
Project 1	23-Oct-17	4
Project 1	23-Oct-17	3
Project 1	23-Oct-17	1.25
Project 1	30-Oct-17	15
Project 1	30-Oct-17	7.5
Project 1	30-Oct-17	9.5
Project 1	30-Oct-17	1.25
Project 1	30-Oct-17	2.5
Project 1	30-Oct-17	37.5
Total for	02-Oct-17	57.00
Total for	09-Oct-17	54.00
Total for	16-Oct-17	27.75
Total for	23-Oct-17	50.50
Total for	30-Oct-17	73.25

<tbody>
</tbody><colgroup><col><col><col></colgroup>

 

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