Calculate Monthy value in a year after inputing year

[bootdat]

I have a table that contains data

POSv2.xlsm
	A	B	C	D	E	F
1	Invoice Number	Date	Qty	Item Description	Unit Price	Total
2	x
3	JAN210002	1/4/2021	2	6 inches round 3 flavours ,SUGAR SHEET	200	400
4	JAN210002	1/4/2021	4	6 inches round 3 flavours ,SUGAR SHEET	200	800
5	JAN210002	1/4/2021	1	6 INCHES ROUND 3 FLAVOURS CUSTOM(FLOWER)	220	200
6	JAN210003	1/4/2021	1	6 inches round - 3 flavours hidden image effect	220	220
7	JAN210003	1/4/2021	1	6 INCHES ROUND 3 FLAVOURS CUSTOM(FLOWER)	220	220
8	JAN210004	1/4/2021	1	6 inches round - 3 Flavors (More Toppings)	200	200
9	JAN210001	1/4/2021	1	6 inches round 3 flavours ,SUGAR SHEET	200	200
10	JAN210001	1/4/2021	1	6 inches round - 3 Flavors (More Toppings)	200	200
11	JAN210001	1/4/2021	2	6 inches round - 4 Flavors	250	500
12	JAN2100002	1/4/2021	1	6 inches round - 3 flavours hidden image effect	220	220
13	JAN2100003	1/4/2021	5	6 inches round 3 flavours ,SUGAR SHEET	200	500
14	JAN2100004	1/4/2021	1	6 inches round - 3 Flavors (More Toppings)	200	195
15	JAN2100004	1/4/2021	50	Meat turnovers (Big)	5	238
16	JAN2100004	1/4/2021	1	Sausage rolls (Small) 12 pieces)	20	20
17	JAN2100004	1/4/2021	2	Fried chips (Olonka)	50	95
18	JAN2100005	1/5/2021	1	6 Inch Whipped Cream Cake [Rd]	200	200
19	JAN2100005	1/5/2021	1	Breakfast Basket Medium [6 Heads]	450	400
20	JAN2100005	1/5/2021	20	Mini Beef Burger	7	140
21	JAN2100005	1/5/2021	10	Chicken Lollipop	10	100
22	JAN2100006	1/5/2021	1	7 Inch Whipped Cream Cake [Rd]	250	250
23	JAN2100006	1/5/2021	10	Cornish Pie	5	50
24	JAN2100006	1/5/2021	1	Lunch Basket Platinum [10-12 Heads]	1000	950
Invoice Data

And id like to know the total sum of each month every year by inputing the year

POSv2.xlsm
	C	D	E
5	Year		2021
6
7	Date		Revenue
8	January
9	February
10	March
11	April
12	May
13	June
14	July
15	August
16	September
17	October
18	November
19	December
Sheet2

Which formula can i use to get the values of each month when i put in the year there?

 

[bbotzong]

SUMPRODUCT() function will do this. In order to simplify, I added the month number in Column D of the second table (result set) and put a formula in cell E8 of the second table. The formula is =SUMPRODUCT(($F$3:$F$24)*(MONTH($B3:$B$24)=C8)

This can be copied down from E8 to E19. Provided you have more than one month of data in your table, it will calculate the column F totals for each month. Of course, you'll have to adjust the $F$24 and the $B$24 to encompass the entire range.

Hope this helps.

bb

 

[jasonb75]

Sumproduct is inefficient with large volumes of data. SUMIF or SUMIFS would be a preferable choice.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Invoice Number	Date	Qty	Item Description	Unit Price	Total		Year		2021
2	JAN210002	04/01/2021	2	6 inches round 3 flavours ,SUGAR SHEET	200	400
3	JAN210002	04/01/2021	4	6 inches round 3 flavours ,SUGAR SHEET	200	800		Date		Revenue
4	JAN210002	04/01/2021	1	6 INCHES ROUND 3 FLAVOURS CUSTOM(FLOWER)	220	200		January		6298
5	JAN210003	04/01/2021	1	6 inches round - 3 flavours hidden image effect	220	220		February		0
6	JAN210003	04/01/2021	1	6 INCHES ROUND 3 FLAVOURS CUSTOM(FLOWER)	220	220		March		0
7	JAN210004	04/01/2021	1	6 inches round - 3 Flavors (More Toppings)	200	200		April		0
8	JAN210001	04/01/2021	1	6 inches round 3 flavours ,SUGAR SHEET	200	200		May		0
9	JAN210001	04/01/2021	1	6 inches round - 3 Flavors (More Toppings)	200	200		June		0
10	JAN210001	04/01/2021	2	6 inches round - 4 Flavors	250	500		July		0
11	JAN2100002	04/01/2021	1	6 inches round - 3 flavours hidden image effect	220	220		August		0
12	JAN2100003	04/01/2021	5	6 inches round 3 flavours ,SUGAR SHEET	200	500		September		0
13	JAN2100004	04/01/2021	1	6 inches round - 3 Flavors (More Toppings)	200	195		October		0
14	JAN2100004	04/01/2021	50	Meat turnovers (Big)	5	238		November		0
15	JAN2100004	04/01/2021	1	Sausage rolls (Small) 12 pieces)	20	20		December		0
16	JAN2100004	04/01/2021	2	Fried chips (Olonka)	50	95
17	JAN2100005	05/01/2021	1	6 Inch Whipped Cream Cake [Rd]	200	200
18	JAN2100005	05/01/2021	1	Breakfast Basket Medium [6 Heads]	450	400
19	JAN2100005	05/01/2021	20	Mini Beef Burger	7	140
20	JAN2100005	05/01/2021	10	Chicken Lollipop	10	100
21	JAN2100006	05/01/2021	1	7 Inch Whipped Cream Cake [Rd]	250	250
22	JAN2100006	05/01/2021	10	Cornish Pie	5	50
23	JAN2100006	05/01/2021	1	Lunch Basket Platinum [10-12 Heads]	1000	950
Sheet1

Cell Formulas
Range	Formula
J4:J15	J4	=SUMIFS(F:F,B:B,">="&DATEVALUE(H4&$J$1),B:B,"<="&EOMONTH(DATEVALUE(H4&$J$1),0))

 

[bootdat]

Sumproduct is inefficient with large volumes of data. SUMIF or SUMIFS would be a preferable choice.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Invoice Number	Date	Qty	Item Description	Unit Price	Total		Year		2021
2	JAN210002	04/01/2021	2	6 inches round 3 flavours ,SUGAR SHEET	200	400
3	JAN210002	04/01/2021	4	6 inches round 3 flavours ,SUGAR SHEET	200	800		Date		Revenue
4	JAN210002	04/01/2021	1	6 INCHES ROUND 3 FLAVOURS CUSTOM(FLOWER)	220	200		January		6298
5	JAN210003	04/01/2021	1	6 inches round - 3 flavours hidden image effect	220	220		February		0
6	JAN210003	04/01/2021	1	6 INCHES ROUND 3 FLAVOURS CUSTOM(FLOWER)	220	220		March		0
7	JAN210004	04/01/2021	1	6 inches round - 3 Flavors (More Toppings)	200	200		April		0
8	JAN210001	04/01/2021	1	6 inches round 3 flavours ,SUGAR SHEET	200	200		May		0
9	JAN210001	04/01/2021	1	6 inches round - 3 Flavors (More Toppings)	200	200		June		0
10	JAN210001	04/01/2021	2	6 inches round - 4 Flavors	250	500		July		0
11	JAN2100002	04/01/2021	1	6 inches round - 3 flavours hidden image effect	220	220		August		0
12	JAN2100003	04/01/2021	5	6 inches round 3 flavours ,SUGAR SHEET	200	500		September		0
13	JAN2100004	04/01/2021	1	6 inches round - 3 Flavors (More Toppings)	200	195		October		0
14	JAN2100004	04/01/2021	50	Meat turnovers (Big)	5	238		November		0
15	JAN2100004	04/01/2021	1	Sausage rolls (Small) 12 pieces)	20	20		December		0
16	JAN2100004	04/01/2021	2	Fried chips (Olonka)	50	95
17	JAN2100005	05/01/2021	1	6 Inch Whipped Cream Cake [Rd]	200	200
18	JAN2100005	05/01/2021	1	Breakfast Basket Medium [6 Heads]	450	400
19	JAN2100005	05/01/2021	20	Mini Beef Burger	7	140
20	JAN2100005	05/01/2021	10	Chicken Lollipop	10	100
21	JAN2100006	05/01/2021	1	7 Inch Whipped Cream Cake [Rd]	250	250
22	JAN2100006	05/01/2021	10	Cornish Pie	5	50
23	JAN2100006	05/01/2021	1	Lunch Basket Platinum [10-12 Heads]	1000	950
Sheet1

Cell Formulas
Range	Formula
J4:J15	J4	=SUMIFS(F:F,B:B,">="&DATEVALUE(H4&$J$1),B:B,"<="&EOMONTH(DATEVALUE(H4&$J$1),0))

I'm not behind the pc apparently, but I'm pretty sure that's what I'm looking for.
Thanks very much.
Regards

 

[bootdat]

Y

I'm not behind the pc apparently, but I'm pretty sure that's what I'm looking for.
Thanks very much.
Regards

Yes it did work. Thanks again!