sum months excluding months

excelstudentJBOH

New Member
Joined
Aug 24, 2017
Messages
11
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Product[/TD]
[TD]$/product[/TD]
[TD]Quantity sold[/TD]
[TD]Total Amount Sold[/TD]
[/TR]
[TR]
[TD]01/01/2017[/TD]
[TD]earrings[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]$25[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]bracelets[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]$60[/TD]
[/TR]
[TR]
[TD]01/03/2017[/TD]
[TD]rings[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]$21[/TD]
[/TR]
[TR]
[TD]02/01/2017[/TD]
[TD]earrings[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]$30[/TD]
[/TR]
[TR]
[TD]02/02/2017[/TD]
[TD]bracelets[/TD]
[TD]6[/TD]
[TD]11[/TD]
[TD]$66[/TD]
[/TR]
[TR]
[TD]02/03/2017[/TD]
[TD]rings[/TD]
[TD]7[/TD]
[TD]4[/TD]
[TD]$28[/TD]
[/TR]
[TR]
[TD]03/01/2017[/TD]
[TD]earrings[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]$35[/TD]
[/TR]
[TR]
[TD]03/02/2017[/TD]
[TD]bracelets[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD]$72[/TD]
[/TR]
[TR]
[TD]03/03/2017[/TD]
[TD]rings[/TD]
[TD]7[/TD]
[TD]4[/TD]
[TD]$28[/TD]
[/TR]
</tbody>[/TABLE]

Good day to all,:cool:

I want to know if someone can help solve this problem.

I want to sum all sales from earrings and others respectively,
like in the table sales(Jan+Mar) excluding Feb

Any help will be greatly appreciated
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
simple pivot table then exclude feb
 
Upvote 0
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A
[/TD]
[TD]
B
[/TD]
[TD]
C
[/TD]
[TD]
D
[/TD]
[TD]
E
[/TD]
[TD]
F
[/TD]
[TD]
G
[/TD]
[/TR]
[TR]
[TD]
1
[/TD]
[TD]Date[/TD]
[TD]Product[/TD]
[TD]$/product[/TD]
[TD]Quantity sold[/TD]
[TD]Total Amount Sold[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
2
[/TD]
[TD]
1/1/2017
[/TD]
[TD]earrings[/TD]
[TD]
5
[/TD]
[TD]
5
[/TD]
[TD]
$25
[/TD]
[TD][/TD]
[TD]
241​
[/TD]
[/TR]
[TR]
[TD]
3
[/TD]
[TD]
1/2/2017
[/TD]
[TD]bracelets[/TD]
[TD]
6
[/TD]
[TD]
10
[/TD]
[TD]
$60
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
4
[/TD]
[TD]
1/3/2017
[/TD]
[TD]rings[/TD]
[TD]
7
[/TD]
[TD]
3
[/TD]
[TD]
$21
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
5
[/TD]
[TD]
2/1/2017
[/TD]
[TD]earrings[/TD]
[TD]
5
[/TD]
[TD]
6
[/TD]
[TD]
$30
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
6
[/TD]
[TD]
2/2/2017
[/TD]
[TD]bracelets[/TD]
[TD]
6
[/TD]
[TD]
11
[/TD]
[TD]
$66
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
7
[/TD]
[TD]
2/3/2017
[/TD]
[TD]rings[/TD]
[TD]
7
[/TD]
[TD]
4
[/TD]
[TD]
$28
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
8
[/TD]
[TD]
3/1/2017
[/TD]
[TD]earrings[/TD]
[TD]
5
[/TD]
[TD]
7
[/TD]
[TD]
$35
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
9
[/TD]
[TD]
3/2/2017
[/TD]
[TD]bracelets[/TD]
[TD]
6
[/TD]
[TD]
12
[/TD]
[TD]
$72
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
10
[/TD]
[TD]
3/3/2017
[/TD]
[TD]rings[/TD]
[TD]
7
[/TD]
[TD]
4
[/TD]
[TD]
$28
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


G2=
SUMPRODUCT((MONTH(A2:A10)<>2)*(E2:E10))
 
Upvote 0
The thing is that I have a bigger table and I don´t want a pivot table.

Plus, I want the the sum of each product individually by months Jan+Mar excluding those of Feb

Tnx for the replies
 
Upvote 0
This is month wise +Product wise summary you can add/less month / product according to your requirments!

Copy past formula in H3 and paste across right down H3:J14.

Formula

Code:
=SUMIFS($E:$E,$A:$A,">="&$G3,$A:$A,"<="&EOMONTH($G3,0),$B:$B,H$2)



[TABLE="width: 416"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD] G [/TD]
[TD] H[/TD]
[TD] I[/TD]
[TD] J[/TD]
[TD] K[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]earrings[/TD]
[TD]bracelets[/TD]
[TD]rings[/TD]
[TD] Total[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]January-17[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]106[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]February-17[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]124[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]March-17[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]135[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]April-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]May-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]June-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]July-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]August-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]September-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]October-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]November-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]December-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: left"]Total[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]198[/TD]
[TD="align: right"]77[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A
[/TD]
[TD]
B
[/TD]
[TD]
C
[/TD]
[TD]
D
[/TD]
[TD]
E
[/TD]
[TD]
F
[/TD]
[TD]
G
[/TD]
[TD]
H
[/TD]
[TD]
I
[/TD]
[TD]
J
[/TD]
[TD]
K
[/TD]
[/TR]
[TR]
[TD]
1
[/TD]
[TD]Date[/TD]
[TD]Product[/TD]
[TD]$/product[/TD]
[TD]Quantity sold[/TD]
[TD]Total Amount Sold[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$/product[/TD]
[TD]Quantity sold[/TD]
[TD]Total Amount Sold[/TD]
[/TR]
[TR]
[TD]
2
[/TD]
[TD]
1/1/2017​
[/TD]
[TD]earrings[/TD]
[TD]
5​
[/TD]
[TD]
5​
[/TD]
[TD]
25​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]earrings[/TD]
[TD]
10​
[/TD]
[TD]
12​
[/TD]
[TD]
60​
[/TD]
[/TR]
[TR]
[TD]
3
[/TD]
[TD]
1/2/2017​
[/TD]
[TD]bracelets[/TD]
[TD]
6​
[/TD]
[TD]
10​
[/TD]
[TD]
60​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]bracelets[/TD]
[TD]
12​
[/TD]
[TD]
22​
[/TD]
[TD]
132​
[/TD]
[/TR]
[TR]
[TD]
4
[/TD]
[TD]
1/3/2017​
[/TD]
[TD]rings[/TD]
[TD]
7​
[/TD]
[TD]
3​
[/TD]
[TD]
21​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]rings[/TD]
[TD]
14​
[/TD]
[TD]
7​
[/TD]
[TD]
23​
[/TD]
[/TR]
[TR]
[TD]
5
[/TD]
[TD]
2/1/2017​
[/TD]
[TD]earrings[/TD]
[TD]
5​
[/TD]
[TD]
6​
[/TD]
[TD]
30​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
6
[/TD]
[TD]
2/2/2017​
[/TD]
[TD]bracelets[/TD]
[TD]
6​
[/TD]
[TD]
11​
[/TD]
[TD]
66​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
7
[/TD]
[TD]
2/3/2017​
[/TD]
[TD]rings[/TD]
[TD]
7​
[/TD]
[TD]
4​
[/TD]
[TD]
28​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
8
[/TD]
[TD]
3/1/2017​
[/TD]
[TD]earrings[/TD]
[TD]
5​
[/TD]
[TD]
7​
[/TD]
[TD]
35​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
9
[/TD]
[TD]
3/2/2017​
[/TD]
[TD]bracelets[/TD]
[TD]
6​
[/TD]
[TD]
12​
[/TD]
[TD]
72​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
10
[/TD]
[TD]
3/3/2017​
[/TD]
[TD]rings[/TD]
[TD]
7​
[/TD]
[TD]
4​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I2
=SUMPRODUCT(($B$2:$B$10=$H2)*($C$1:$F$1=I$1)*(MONTH($A$2:$A$10)<>2)*(C$2:C$10)) copy across and down
 
Upvote 0
Thank you all for your responses.
MARZIOTULLIO provided what I was looking for.

I was going crazy trying to figure it out with SUMIFS
 
Upvote 0

Forum statistics

Threads
1,224,974
Messages
6,182,108
Members
453,088
Latest member
Chaoxite

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
Back
Top