sum months excluding months

excelstudentJBOH

New Member
Joined
Aug 24, 2017
Messages
11
DateProduct$/productQuantity soldTotal Amount Sold
01/01/2017earrings55$25
01/02/2017bracelets610$60
01/03/2017rings73$21
02/01/2017earrings56$30
02/02/2017bracelets611$66
02/03/2017rings74$28
03/01/2017earrings57$35
03/02/2017bracelets612$72
03/03/2017rings74$28

<tbody>
</tbody>

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
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
9,940
Office Version
2019, 2016, 2013
Platform
Windows
simple pivot table then exclude feb
 

MARZIOTULLIO

Well-known Member
Joined
Aug 22, 2015
Messages
767
A
B
C
D
E
F
G
1
DateProduct$/productQuantity soldTotal Amount Sold
2
1/1/2017
earrings
5
5
$25
241​
3
1/2/2017
bracelets
6
10
$60
4
1/3/2017
rings
7
3
$21
5
2/1/2017
earrings
5
6
$30
6
2/2/2017
bracelets
6
11
$66
7
2/3/2017
rings
7
4
$28
8
3/1/2017
earrings
5
7
$35
9
3/2/2017
bracelets
6
12
$72
10
3/3/2017
rings
7
4
$28

<tbody>
</tbody>


G2=
SUMPRODUCT((MONTH(A2:A10)<>2)*(E2:E10))
 

excelstudentJBOH

New Member
Joined
Aug 24, 2017
Messages
11
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
 

Muhammad_Usman

Active Member
Joined
Dec 9, 2015
Messages
324
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)


G H I J K
earringsbraceletsrings Total
January-17256021106
February-17306628124
March-17357228135
April-17 0
May-17 0
June-17 0
July-17 0
August-17 0
September-17 0
October-17 0
November-17 0
December-17 0
0
Total9019877

<colgroup><col><col><col span="4"></colgroup><tbody>
</tbody>
 

MARZIOTULLIO

Well-known Member
Joined
Aug 22, 2015
Messages
767
A
B
C
D
E
F
G
H
I
J
K
1
DateProduct$/productQuantity soldTotal Amount Sold$/productQuantity soldTotal Amount Sold
2
1/1/2017​
earrings
5​
5​
25​
earrings
10​
12​
60​
3
1/2/2017​
bracelets
6​
10​
60​
bracelets
12​
22​
132​
4
1/3/2017​
rings
7​
3​
21​
rings
14​
7​
23​
5
2/1/2017​
earrings
5​
6​
30​
6
2/2/2017​
bracelets
6​
11​
66​
7
2/3/2017​
rings
7​
4​
28​
8
3/1/2017​
earrings
5​
7​
35​
9
3/2/2017​
bracelets
6​
12​
72​
10
3/3/2017​
rings
7​
4​
2​

<tbody>
</tbody>


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
 

excelstudentJBOH

New Member
Joined
Aug 24, 2017
Messages
11
Thank you all for your responses.
MARZIOTULLIO provided what I was looking for.

I was going crazy trying to figure it out with SUMIFS
 

Forum statistics

Threads
1,085,329
Messages
5,382,986
Members
401,812
Latest member
emelyan1379

Some videos you may like

This Week's Hot Topics

Top