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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
simple pivot table then exclude feb
 
Upvote 0

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))
 
Upvote 0

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
 
Upvote 0

Muhammad_Usman

Active Member
Joined
Dec 9, 2015
Messages
367
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>
 
Upvote 0

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
 
Upvote 0

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
 
Upvote 0

Forum statistics

Threads
1,187,107
Messages
5,961,629
Members
438,556
Latest member
darrylburge

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