Sumifs multiple criteria

[Zandra]

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	AMOUNT	Column1
01/01/2016		4000		12	0.00
02/01/2016		7100		1	3000.00
02/01/2016		2100		12	-3000.00
02/01/2016		1353		0	-800.00
02/01/2016		4003		1	800.00
02/01/2016		1353		0	-1415.42
02/01/2016		7150		14	83.00
17/01/2016		4000		1	-55.90
02/01/2016		4900		7	-800.00
20/02/2016		4024		1	800.00
02/01/2016		4900		7	167.29
31/01/2016		4155		1	-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
Sales	4000	4003		#VALUE!	744.10
Consult Sales	4024	4026		0.00	800.00
Less Consults	4150	4160		0.00	-167.29
Other Sales	4900	4910		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>

 

[igold]

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]

Added with columns

	A	B	C	D
1	DATE	NOMINAL_CODE	DEPT_NUMBER	AMOUNT
2	01/01/2016	4000	12	0.00
3	02/01/2016	7100	1	3000.00
4	02/01/2016	2100	12	-3000.00
5	02/01/2016	1353	0	-800.00
6	02/01/2016	4003	1	800.00
7	02/01/2016	1353	0	-1415.42
8	02/01/2016	7150	14	83.00
9	02/01/2016	2201	14	0.00
10	05/02/2016	2100	15	-82.89
11	02/01/2016	7100	7	-2084.00
12	17/01/2016	4000	1	-55.90
13	02/01/2016	4900	7	-800.00
14	20/02/2016	4024	1	800.00
15	02/01/2016	2201	2	0.00
16	02/01/2016	4900	7	167.29
17	31/01/2016	4155	1	-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>

	A	B	C	D	E
1					Dept
2					1
3	GPM				40.5%
4						RESULTS I WANT
5	SALES
6	Sales	4000	4003		#VALUE!	744.10
7	Consult Sales	4024	4026		0.00	800.00
8	Less Consults	4150	4160		0.00	-167.29
9	Other Sales	4900	4910		0.00	-632.71
10	NET 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]

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	DATE	NOMINAL_CODE	DEPT_NUMBER	AMOUNT			DATE	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]

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>