Extracting data from certains dates

[Nelly3007]

Hello All,

I have a spreadsheet where production is recorded each day and a monthly figure is also needed. I currently have a formula in place in cell H3 to add up all of January’s figures. Is there a way or a formula that I could put here instead so that it will add up if the data automatically if column B has a January date in? My intention is to use this spreadsheet every year and therefore I could make a copy and the formulas will still work and still add up January dates without any re-working. Hope that all makes sense.

Book1
	A	B	C	D	E	F	G	H
1	WEEK	DATE	DAY	DAILY TONNAGE SUGAR
2	52	26/12/2022	MONDAY				Month	Total Tonnage Sugar
3		27/12/2022	TUESDAY				January (52-4)	1830
4		28/12/2022	WEDNESDAY				February (5-8)
5		29/12/2022	THURSDAY				March (9-13)
6		30/12/2022	FRIDAY				April (14-17)
7		31/12/2022	SATURDAY				May (18-21)
8		01/01/2023	SUNDAY				June (22-26)
9	1	02/01/2023	MONDAY	0			July (27-30)
10		03/01/2023	TUESDAY	0			August (31-34)
11		04/01/2023	WEDNESDAY	0			September (35-39)
12		05/01/2023	THURSDAY	0			October (40-43)
13		06/01/2023	FRIDAY	0			November (44-47)
14		07/01/2023	SATURDAY				December (48-52)
15		08/01/2023	SUNDAY
16	2	09/01/2023	MONDAY	0
17		10/01/2023	TUESDAY	0
18		11/01/2023	WEDNESDAY	23
19		12/01/2023	THURSDAY	155
20		13/01/2023	FRIDAY	362
21		14/01/2023	SATURDAY
22		15/01/2023	SUNDAY
23	3	16/01/2023	MONDAY	270.00
24		17/01/2023	TUESDAY	216.00
25		18/01/2023	WEDNESDAY	327.00
26		19/01/2023	THURSDAY	157.00
27		20/01/2023	FRIDAY	320.00
28		21/01/2023	SATURDAY
29		22/01/2023	SUNDAY
30	4	23/01/2023	MONDAY
31		24/01/2023	TUESDAY
32		25/01/2023	WEDNESDAY
33		26/01/2023	THURSDAY
34		27/01/2023	FRIDAY
35		28/01/2023	SATURDAY
36		29/01/2023	SUNDAY
37	5	30/01/2023	MONDAY
38		31/01/2023	TUESDAY
39		01/02/2023	WEDNESDAY
40		02/02/2023	THURSDAY
41		03/02/2023	FRIDAY
42		04/02/2023	SATURDAY
43		05/02/2023	SUNDAY
44	6	06/02/2023	MONDAY
45		07/02/2023	TUESDAY
46		08/02/2023	WEDNESDAY
47		09/02/2023	THURSDAY
48		10/02/2023	FRIDAY
49		11/02/2023	SATURDAY
50		12/02/2023	SUNDAY
Sheet1

Cell Formulas
Range	Formula
H3	H3	=SUM(D8:D38)

 

[Fluff]

How about

=SUMPRODUCT(--(MONTH($B$2:$B$100)=ROWS(H$3:H3)),$D$2:$D$100)

 

[Nelly3007]

Thanks so much that works! Do you know how to make this work over two sheets? So I could have the weekly data on sheet 1 and monthly data on sheet 2 but still have the same result?

Many Thanks,

 

[Fluff]

You would be better off keeping everything on one sheet, it makes things a lot simpler.

 

[Nelly3007]

Thats what management have now requested so we can have much more data available to view you think it would be easier to use the manual formula that I've already got in H3 if we are to do it over two sheets?

 

[Fluff]

If you are simply wanting to put the formula on another sheet, then you just nee t add the sheet name to the ranges.