Sumifs multiple criteria

Zandra

New Member
Joined
Dec 18, 2015
Messages
7
I HAVE A NOMINAL TABLE AND I WANT TO CREATE A TABLE OF VALUES WITHIN GIVEN CRITERIA. I HAVE TRIED VARIOUS VARIATIONS BUT CANNOT GET IT TO WORK. FOR EXAMPLE I WANT TO SUM ALL AMOUNTS WITHIN THE DATE RANGE 02/01/16-31/01/16, NOMINAL RANGE 4000 TO 4003, WITHIN DEPT 1. HERE IS AN EXAMPLE:

DATE RANGE 02/01/16-31/01/16

DATE
NOMINAL
DEPT_NUMBER
AMOUNTColumn1
01/01/2016
4000120.00
02/01/2016710013000.00
02/01/2016210012-3000.00
02/01/201613530-800.00
02/01/201640031800.00
02/01/201613530-1415.42
02/01/201671501483.00
17/01/2016
40001-55.90
02/01/201649007-800.00
20/02/201640241800.00
02/01/2016
49007167.29
31/01/201641551-167.29

<colgroup><col style="mso-width-source:userset;mso-width-alt:4608;width:95pt" width="126"> <col style="width:48pt" span="4" width="64"> </colgroup><tbody>
</tbody>

Dept
1
SALES
RESULTS I WANT
Sales40004003 #VALUE!744.10
Consult Sales40244026 0.00800.00
Less Consults41504160 0.00-167.29
Other Sales49004910 0.00-632.71
NET SALES

<colgroup><col style="mso-width-source:userset;mso-width-alt:3693;width:76pt" width="101"> <col style="width:48pt" span="5" width="64"> </colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,506
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi Zandra,

If you supply us with some column letters and row numbers on your before and after, it would help us to give you a working formula a lot easier...

igold
 

Zandra

New Member
Joined
Dec 18, 2015
Messages
7
Added with columns

ABCD
1
DATENOMINAL_CODEDEPT_NUMBERAMOUNT
201/01/20164000120.00
302/01/2016710013000.00
402/01/2016210012-3000.00
502/01/201613530-800.00
602/01/201640031800.00
702/01/201613530-1415.42
802/01/201671501483.00
902/01/20162201140.00
1005/02/2016210015-82.89
1102/01/201671007-2084.00
1217/01/201640001-55.90
1302/01/201649007-800.00
1420/02/201640241800.00
1502/01/2016220120.00
1602/01/201649007167.29
1731/01/2016
41551-167.29


<colgroup><col style="mso-width-source:userset;mso-width-alt:987;width:20pt" width="27"> <col style="mso-width-source:userset;mso-width-alt:4608;width:95pt" width="126"> <col style="width:48pt" span="4" width="64"> </colgroup><tbody>
</tbody>

ABCDE
1
Dept
2 1
3GPM40.5%
4 RESULTS I WANT
5SALES
6Sales40004003 #VALUE!744.10
7Consult Sales40244026 0.00800.00
8Less Consults41504160 0.00-167.29
9Other Sales49004910 0.00-632.71
10NET SALES

<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3693;width:76pt" width="101"> <col style="width:48pt" span="6" width="64"> </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
DATENOMINAL_CODEDEPT_NUMBERAMOUNTDATE
1/2/2016​
1/31/2016​
Dept
2
1/1/2016​
4000​
12​
0​
1​
3
1/2/2016​
7100​
1​
3000​
GPM
40.50%​
4
1/2/2016​
2100​
12​
-3000​
5
1/2/2016​
1353​
0​
-800​
SALES
6
1/2/2016​
4003​
1​
800​
Sales
4000​
4003​
744.1​
7
1/2/2016​
1353​
0​
-1415.42​
Consult Sales
4024​
4026​
0​
8
1/2/2016​
7150​
14​
83​
Less Consults
4150​
4160​
-167.29​
9
1/2/2016​
2201​
14​
0​
Other Sales
4900​
4910​
0​
10
2/5/2016​
2100​
15​
-82.89​
NET SALES
11
1/2/2016​
7100​
7​
-2084​
12
1/17/2016​
4000​
1​
-55.9​
13
1/2/2016​
4900​
7​
-800​
14
2/20/2016​
4024​
1​
800​
15
1/2/2016​
2201​
2​
0​
16
1/2/2016​
4900​
7​
167.29​
17
1/31/2016​
4155​
1​
-167.29​
18

<tbody>
</tbody>

K6 =SUMPRODUCT(($A$2:$A$17>=$H$1)*($A$2:$A$17<=$I$1)*($C$2:$C$17=$K$2)*($B$2:$B$17>=H6)*($B$2:$B$17<=I6),$D$2:$D$17) Copy Down

<tbody>
</tbody>
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
More efficient...

Sheet1

Row\Col
A​
B​
C​
D​
1​
DATE NOMINAL_CODE DEPT_NUMBER AMOUNT
2​
1/1/2016
4000 12
0
3​
1/2/2016
7100 1
3000
4​
1/2/2016
2100 12
-3000
5​
1/2/2016
1353 0
-800
6​
1/2/2016
4003 1
800
7​
1/2/2016
1353 0
-1415.42
8​
1/2/2016
7150 14
83
9​
1/2/2016
2201 14
0
10​
2/5/2016
2100 15
-82.89
11​
1/2/2016
7100 7
-2084
12​
1/17/2016
4000 1
-55.9
13​
1/2/2016
4900 7
-800
14​
2/20/2016
4024 1
800
15​
1/2/2016
2201 2
0
16​
1/2/2016
4900 7
167.29
17​
1/31/2016
4155 1
-167.29

Sheet2

Row\Col
A​
B​
C​
D​
E​
F​
1​
Dept
2​
1
3​
GPM 40.50%
4​
RESULTS I WANT
5​
SALES
6​
Sales
4000
4003
744.1​
7​
Consult Sales
4024
4026
800​
8​
Less Consults
4150
4160
-167.29​
9​
Other Sales
4900
4910
0​
10​
NET SALES

In E6 enter and copy down:

=SUMPRODUCT(SUMIFS(Sheet1!D:D,Sheet1!C:C,E$2,Sheet1!B:B,">="&$B6,Sheet1!B:B,"<="&$C6))

(Note. When posting a sample, try to omit irrelevant distracting details like GPM, etc.)


<strike></strike>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,685
Messages
5,597,532
Members
414,152
Latest member
ReservoirDodds

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