Calculate Monthy value in a year after inputing year

bootdat

New Member
Joined
Nov 22, 2018
Messages
44
I have a table that contains data

POSv2.xlsm
ABCDEF
1Invoice NumberDateQtyItem DescriptionUnit PriceTotal
2x
3JAN2100021/4/202126 inches round 3 flavours ,SUGAR SHEET200400
4JAN2100021/4/202146 inches round 3 flavours ,SUGAR SHEET200800
5JAN2100021/4/202116 INCHES ROUND 3 FLAVOURS CUSTOM(FLOWER)220200
6JAN2100031/4/202116 inches round - 3 flavours hidden image effect220220
7JAN2100031/4/202116 INCHES ROUND 3 FLAVOURS CUSTOM(FLOWER)220220
8JAN2100041/4/202116 inches round - 3 Flavors (More Toppings)200200
9JAN2100011/4/202116 inches round 3 flavours ,SUGAR SHEET200200
10JAN2100011/4/202116 inches round - 3 Flavors (More Toppings)200200
11JAN2100011/4/202126 inches round - 4 Flavors 250500
12JAN21000021/4/202116 inches round - 3 flavours hidden image effect220220
13JAN21000031/4/202156 inches round 3 flavours ,SUGAR SHEET200500
14JAN21000041/4/202116 inches round - 3 Flavors (More Toppings)200195
15JAN21000041/4/202150Meat turnovers (Big)5238
16JAN21000041/4/20211Sausage rolls (Small) 12 pieces)2020
17JAN21000041/4/20212Fried chips (Olonka)5095
18JAN21000051/5/202116 Inch Whipped Cream Cake [Rd]200200
19JAN21000051/5/20211Breakfast Basket Medium [6 Heads]450400
20JAN21000051/5/202120Mini Beef Burger7140
21JAN21000051/5/202110Chicken Lollipop10100
22JAN21000061/5/202117 Inch Whipped Cream Cake [Rd]250250
23JAN21000061/5/202110Cornish Pie550
24JAN21000061/5/20211Lunch Basket Platinum [10-12 Heads]1000950
Invoice Data


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

POSv2.xlsm
CDE
5Year2021
6
7DateRevenue
8January
9February
10March
11April
12May
13June
14July
15August
16September
17October
18November
19December
Sheet2


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

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

bbotzong

Board Regular
Joined
Dec 17, 2003
Messages
56
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows
Sumproduct is inefficient with large volumes of data. SUMIF or SUMIFS would be a preferable choice.

Book1
ABCDEFGHIJ
1Invoice NumberDateQtyItem DescriptionUnit PriceTotalYear2021
2JAN21000204/01/202126 inches round 3 flavours ,SUGAR SHEET200400
3JAN21000204/01/202146 inches round 3 flavours ,SUGAR SHEET200800DateRevenue
4JAN21000204/01/202116 INCHES ROUND 3 FLAVOURS CUSTOM(FLOWER)220200January6298
5JAN21000304/01/202116 inches round - 3 flavours hidden image effect220220February0
6JAN21000304/01/202116 INCHES ROUND 3 FLAVOURS CUSTOM(FLOWER)220220March0
7JAN21000404/01/202116 inches round - 3 Flavors (More Toppings)200200April0
8JAN21000104/01/202116 inches round 3 flavours ,SUGAR SHEET200200May0
9JAN21000104/01/202116 inches round - 3 Flavors (More Toppings)200200June0
10JAN21000104/01/202126 inches round - 4 Flavors 250500July0
11JAN210000204/01/202116 inches round - 3 flavours hidden image effect220220August0
12JAN210000304/01/202156 inches round 3 flavours ,SUGAR SHEET200500September0
13JAN210000404/01/202116 inches round - 3 Flavors (More Toppings)200195October0
14JAN210000404/01/202150Meat turnovers (Big)5238November0
15JAN210000404/01/20211Sausage rolls (Small) 12 pieces)2020December0
16JAN210000404/01/20212Fried chips (Olonka)5095
17JAN210000505/01/202116 Inch Whipped Cream Cake [Rd]200200
18JAN210000505/01/20211Breakfast Basket Medium [6 Heads]450400
19JAN210000505/01/202120Mini Beef Burger7140
20JAN210000505/01/202110Chicken Lollipop10100
21JAN210000605/01/202117 Inch Whipped Cream Cake [Rd]250250
22JAN210000605/01/202110Cornish Pie550
23JAN210000605/01/20211Lunch Basket Platinum [10-12 Heads]1000950
Sheet1
Cell Formulas
RangeFormula
J4:J15J4=SUMIFS(F:F,B:B,">="&DATEVALUE(H4&$J$1),B:B,"<="&EOMONTH(DATEVALUE(H4&$J$1),0))
 
Solution

bootdat

New Member
Joined
Nov 22, 2018
Messages
44
Sumproduct is inefficient with large volumes of data. SUMIF or SUMIFS would be a preferable choice.

Book1
ABCDEFGHIJ
1Invoice NumberDateQtyItem DescriptionUnit PriceTotalYear2021
2JAN21000204/01/202126 inches round 3 flavours ,SUGAR SHEET200400
3JAN21000204/01/202146 inches round 3 flavours ,SUGAR SHEET200800DateRevenue
4JAN21000204/01/202116 INCHES ROUND 3 FLAVOURS CUSTOM(FLOWER)220200January6298
5JAN21000304/01/202116 inches round - 3 flavours hidden image effect220220February0
6JAN21000304/01/202116 INCHES ROUND 3 FLAVOURS CUSTOM(FLOWER)220220March0
7JAN21000404/01/202116 inches round - 3 Flavors (More Toppings)200200April0
8JAN21000104/01/202116 inches round 3 flavours ,SUGAR SHEET200200May0
9JAN21000104/01/202116 inches round - 3 Flavors (More Toppings)200200June0
10JAN21000104/01/202126 inches round - 4 Flavors 250500July0
11JAN210000204/01/202116 inches round - 3 flavours hidden image effect220220August0
12JAN210000304/01/202156 inches round 3 flavours ,SUGAR SHEET200500September0
13JAN210000404/01/202116 inches round - 3 Flavors (More Toppings)200195October0
14JAN210000404/01/202150Meat turnovers (Big)5238November0
15JAN210000404/01/20211Sausage rolls (Small) 12 pieces)2020December0
16JAN210000404/01/20212Fried chips (Olonka)5095
17JAN210000505/01/202116 Inch Whipped Cream Cake [Rd]200200
18JAN210000505/01/20211Breakfast Basket Medium [6 Heads]450400
19JAN210000505/01/202120Mini Beef Burger7140
20JAN210000505/01/202110Chicken Lollipop10100
21JAN210000605/01/202117 Inch Whipped Cream Cake [Rd]250250
22JAN210000605/01/202110Cornish Pie550
23JAN210000605/01/20211Lunch Basket Platinum [10-12 Heads]1000950
Sheet1
Cell Formulas
RangeFormula
J4:J15J4=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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,743
Messages
5,626,620
Members
416,195
Latest member
tonmcg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top