Sumifs multiple criteria

Z

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


<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>
[TD="class: xl65, width: 126"]DATE
[/TD]
[TD="class: xl65, width: 126"][/TD]
[TD="class: xl74, width: 64"]NOMINAL
[/TD]
[TD="class: xl74, width: 64"][/TD]
[TD="class: xl74, width: 64"]DEPT_NUMBER
[/TD]
[TD="class: xl71, width: 64"]AMOUNT[/TD]
[TD="class: xl66, width: 64"]Column1[/TD]

[TD="class: xl67, align: right"]01/01/2016
[/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl75"]4000[/TD]
[TD="class: xl77"][/TD]
[TD="class: xl77"]12[/TD]
[TD="class: xl72, align: right"]0.00[/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl69, align: right"]02/01/2016[/TD]
[TD="class: xl69, align: right"][/TD]
[TD="class: xl76"]7100[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl78"]1[/TD]
[TD="class: xl73, align: right"]3000.00[/TD]
[TD="class: xl70"] [/TD]

[TD="class: xl67, align: right"]02/01/2016[/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl75"]2100[/TD]
[TD="class: xl77"][/TD]
[TD="class: xl77"]12[/TD]
[TD="class: xl72, align: right"]-3000.00[/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl69, align: right"]02/01/2016[/TD]
[TD="class: xl69, align: right"][/TD]
[TD="class: xl76"]1353[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl78"]0[/TD]
[TD="class: xl73, align: right"]-800.00[/TD]
[TD="class: xl70"] [/TD]

[TD="class: xl67, align: right"]02/01/2016[/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl75"]4003[/TD]
[TD="class: xl77"][/TD]
[TD="class: xl77"]1[/TD]
[TD="class: xl79, align: right"]800.00
[/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl69, align: right"]02/01/2016[/TD]
[TD="class: xl69, align: right"][/TD]
[TD="class: xl76"]1353[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl78"]0[/TD]
[TD="class: xl73, align: right"]-1415.42[/TD]
[TD="class: xl70"] [/TD]

[TD="class: xl67, align: right"]02/01/2016[/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl75"]7150[/TD]
[TD="class: xl77"][/TD]
[TD="class: xl77"]14[/TD]
[TD="class: xl72, align: right"]83.00[/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl67, align: right"]17/01/2016
[/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl75"]4000[/TD]
[TD="class: xl77"][/TD]
[TD="class: xl77"]1[/TD]
[TD="class: xl79, align: right"]-55.90[/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl69, align: right"]02/01/2016[/TD]
[TD="class: xl69, align: right"][/TD]
[TD="class: xl76"]4900[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl78"]7[/TD]
[TD="class: xl73, align: right"]-800.00[/TD]
[TD="class: xl70"] [/TD]

[TD="class: xl67, align: right"]20/02/2016[/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl75"]4024[/TD]
[TD="class: xl77"][/TD]
[TD="class: xl77"]1[/TD]
[TD="class: xl72, align: right"]800.00[/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl67, align: right"]02/01/2016
[/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl75"]4900[/TD]
[TD="class: xl77"][/TD]
[TD="class: xl77"]7[/TD]
[TD="class: xl72, align: right"]167.29[/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl69, align: right"]31/01/2016[/TD]
[TD="class: xl69, align: right"][/TD]
[TD="class: xl76"]4155[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl78"]1[/TD]
[TD="class: xl73, align: right"]-167.29[/TD]
[TD="class: xl70"]
[/TD]

</tbody>

RESULTS I WANT

<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>
[TD="class: xl65, width: 101"] [/TD]
[TD="class: xl66, width: 64"] [/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl68, width: 64"]Dept[/TD]
[TD="width: 64"][/TD]

[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl72"]1[/TD]

[TD="class: xl77"]SALES
[/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl79"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl80"] [/TD]

[TD="class: xl69"]Sales[/TD]
[TD="class: xl70, align: right"]4000[/TD]
[TD="class: xl81, align: right"]4003[/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl82, align: center"]#VALUE![/TD]
[TD="class: xl85, align: right"]744.10
[/TD]

[TD="class: xl69"]Consult Sales[/TD]
[TD="align: right"]4024[/TD]
[TD="align: right"]4026[/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl82, align: right"]0.00[/TD]
[TD="class: xl84, align: right"]800.00[/TD]

[TD="class: xl69"]Less Consults[/TD]
[TD="class: xl70, align: right"]4150[/TD]
[TD="class: xl81, align: right"]4160[/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl82, align: right"]0.00[/TD]
[TD="class: xl84, align: right"]-167.29[/TD]

[TD="class: xl69"]Other Sales[/TD]
[TD="class: xl70, align: right"]4900[/TD]
[TD="class: xl81, align: right"]4910[/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl82, align: right"]0.00[/TD]
[TD="class: xl84, align: right"]-632.71[/TD]

[TD="class: xl77"]NET SALES[/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl83"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl82"] [/TD]

</tbody>
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
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
 
Upvote 0
Added with columns


<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>
[TD="width: 27"][/TD]
[TD="class: xl78, width: 126"]A[/TD]
[TD="class: xl78, width: 64"]B[/TD]
[TD="class: xl78, width: 64"]C[/TD]
[TD="class: xl78, width: 64"]D[/TD]
[TD="width: 64"][/TD]

[TD="align: right"]1
[/TD]
[TD="class: xl63"]DATE[/TD]
[TD="class: xl72"]NOMINAL_CODE[/TD]
[TD="class: xl72"]DEPT_NUMBER[/TD]
[TD="class: xl69"]AMOUNT[/TD]
[TD="class: xl64"][/TD]

[TD="align: right"]2[/TD]
[TD="class: xl65, align: right"]01/01/2016[/TD]
[TD="class: xl73"]4000[/TD]
[TD="class: xl75"]12[/TD]
[TD="class: xl70, align: right"]0.00[/TD]
[TD="class: xl66"] [/TD]

[TD="align: right"]3[/TD]
[TD="class: xl67, align: right"]02/01/2016[/TD]
[TD="class: xl74"]7100[/TD]
[TD="class: xl76"]1[/TD]
[TD="class: xl71, align: right"]3000.00[/TD]
[TD="class: xl68"] [/TD]

[TD="align: right"]4[/TD]
[TD="class: xl65, align: right"]02/01/2016[/TD]
[TD="class: xl73"]2100[/TD]
[TD="class: xl75"]12[/TD]
[TD="class: xl70, align: right"]-3000.00[/TD]
[TD="class: xl66"] [/TD]

[TD="align: right"]5[/TD]
[TD="class: xl67, align: right"]02/01/2016[/TD]
[TD="class: xl74"]1353[/TD]
[TD="class: xl76"]0[/TD]
[TD="class: xl71, align: right"]-800.00[/TD]
[TD="class: xl68"] [/TD]

[TD="align: right"]6[/TD]
[TD="class: xl65, align: right"]02/01/2016[/TD]
[TD="class: xl73"]4003[/TD]
[TD="class: xl75"]1[/TD]
[TD="class: xl77, align: right"]800.00[/TD]
[TD="class: xl66"] [/TD]

[TD="align: right"]7[/TD]
[TD="class: xl67, align: right"]02/01/2016[/TD]
[TD="class: xl74"]1353[/TD]
[TD="class: xl76"]0[/TD]
[TD="class: xl71, align: right"]-1415.42[/TD]
[TD="class: xl68"] [/TD]

[TD="align: right"]8[/TD]
[TD="class: xl65, align: right"]02/01/2016[/TD]
[TD="class: xl73"]7150[/TD]
[TD="class: xl75"]14[/TD]
[TD="class: xl70, align: right"]83.00[/TD]
[TD="class: xl66"] [/TD]

[TD="align: right"]9[/TD]
[TD="class: xl67, align: right"]02/01/2016[/TD]
[TD="class: xl74"]2201[/TD]
[TD="class: xl76"]14[/TD]
[TD="class: xl71, align: right"]0.00[/TD]
[TD="class: xl68"] [/TD]

[TD="align: right"]10[/TD]
[TD="class: xl65, align: right"]05/02/2016[/TD]
[TD="class: xl73"]2100[/TD]
[TD="class: xl75"]15[/TD]
[TD="class: xl70, align: right"]-82.89[/TD]
[TD="class: xl66"] [/TD]

[TD="align: right"]11[/TD]
[TD="class: xl67, align: right"]02/01/2016[/TD]
[TD="class: xl74"]7100[/TD]
[TD="class: xl76"]7[/TD]
[TD="class: xl71, align: right"]-2084.00[/TD]
[TD="class: xl68"] [/TD]

[TD="align: right"]12[/TD]
[TD="class: xl65, align: right"]17/01/2016[/TD]
[TD="class: xl73"]4000[/TD]
[TD="class: xl75"]1[/TD]
[TD="class: xl77, align: right"]-55.90[/TD]
[TD="class: xl66"] [/TD]

[TD="align: right"]13[/TD]
[TD="class: xl67, align: right"]02/01/2016[/TD]
[TD="class: xl74"]4900[/TD]
[TD="class: xl76"]7[/TD]
[TD="class: xl71, align: right"]-800.00[/TD]
[TD="class: xl68"] [/TD]

[TD="align: right"]14[/TD]
[TD="class: xl65, align: right"]20/02/2016[/TD]
[TD="class: xl73"]4024[/TD]
[TD="class: xl75"]1[/TD]
[TD="class: xl70, align: right"]800.00[/TD]
[TD="class: xl66"] [/TD]

[TD="align: right"]15[/TD]
[TD="class: xl67, align: right"]02/01/2016[/TD]
[TD="class: xl74"]2201[/TD]
[TD="class: xl76"]2[/TD]
[TD="class: xl71, align: right"]0.00[/TD]
[TD="class: xl68"] [/TD]

[TD="align: right"]16[/TD]
[TD="class: xl65, align: right"]02/01/2016[/TD]
[TD="class: xl73"]4900[/TD]
[TD="class: xl75"]7[/TD]
[TD="class: xl70, align: right"]167.29[/TD]
[TD="class: xl66"] [/TD]

[TD="align: right"]17[/TD]
[TD="class: xl67, align: right"]31/01/2016
[/TD]
[TD="class: xl74"]4155[/TD]
[TD="class: xl76"]1[/TD]
[TD="class: xl71, align: right"]-167.29[/TD]
[TD="class: xl68"]

[/TD]

</tbody>


<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>
[TD="width: 64"][/TD]
[TD="class: xl86, width: 101"]A[/TD]
[TD="class: xl86, width: 64"]B[/TD]
[TD="class: xl86, width: 64"]C[/TD]
[TD="class: xl86, width: 64"]D[/TD]
[TD="class: xl86, width: 64"]E[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]

[TD="align: right"]1
[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68"]Dept[/TD]

[TD="align: right"]2[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl72"]1[/TD]

[TD="align: right"]3[/TD]
[TD="class: xl73"]GPM[/TD]

[TD="class: xl74"][/TD]

[TD="class: xl75"]40.5%[/TD]

[TD="align: right"]4[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl76"] [/TD]
[TD="colspan: 2"]RESULTS I WANT[/TD]

[TD="align: right"]5[/TD]
[TD="class: xl77"]SALES[/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl79"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl80"] [/TD]

[TD="align: right"]6[/TD]
[TD="class: xl69"]Sales[/TD]
[TD="class: xl70, align: right"]4000[/TD]
[TD="class: xl81, align: right"]4003[/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl82, align: center"]#VALUE![/TD]
[TD="class: xl85, align: right"]744.10[/TD]

[TD="align: right"]7[/TD]
[TD="class: xl69"]Consult Sales[/TD]
[TD="align: right"]4024[/TD]
[TD="align: right"]4026[/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl82, align: right"]0.00[/TD]
[TD="class: xl84, align: right"]800.00[/TD]

[TD="align: right"]8[/TD]
[TD="class: xl69"]Less Consults[/TD]
[TD="class: xl70, align: right"]4150[/TD]
[TD="class: xl81, align: right"]4160[/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl82, align: right"]0.00[/TD]
[TD="class: xl84, align: right"]-167.29[/TD]

[TD="align: right"]9[/TD]
[TD="class: xl69"]Other Sales[/TD]
[TD="class: xl70, align: right"]4900[/TD]
[TD="class: xl81, align: right"]4910[/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl82, align: right"]0.00[/TD]
[TD="class: xl84, align: right"]-632.71[/TD]

[TD="align: right"]10[/TD]
[TD="class: xl77"]NET SALES[/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl83"] [/TD]
[TD="class: xl78"] [/TD]
[TD="class: xl82"] [/TD]

</tbody>
 
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]NOMINAL_CODE[/TD]
[TD]DEPT_NUMBER[/TD]
[TD]AMOUNT[/TD]
[TD][/TD]
[TD][/TD]
[TD]DATE[/TD]
[TD]
1/2/2016​
[/TD]
[TD]
1/31/2016​
[/TD]
[TD][/TD]
[TD]Dept[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
1/1/2016​
[/TD]
[TD]
4000​
[/TD]
[TD]
12​
[/TD]
[TD]
0​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]
1/2/2016​
[/TD]
[TD]
7100​
[/TD]
[TD]
1​
[/TD]
[TD]
3000​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]GPM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
40.50%​
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]
1/2/2016​
[/TD]
[TD]
2100​
[/TD]
[TD]
12​
[/TD]
[TD]
-3000​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]
1/2/2016​
[/TD]
[TD]
1353​
[/TD]
[TD]
0​
[/TD]
[TD]
-800​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]SALES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]
1/2/2016​
[/TD]
[TD]
4003​
[/TD]
[TD]
1​
[/TD]
[TD]
800​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sales[/TD]
[TD]
4000​
[/TD]
[TD]
4003​
[/TD]
[TD][/TD]
[TD]
744.1​
[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]
1/2/2016​
[/TD]
[TD]
1353​
[/TD]
[TD]
0​
[/TD]
[TD]
-1415.42​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Consult Sales[/TD]
[TD]
4024​
[/TD]
[TD]
4026​
[/TD]
[TD][/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]
1/2/2016​
[/TD]
[TD]
7150​
[/TD]
[TD]
14​
[/TD]
[TD]
83​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Less Consults[/TD]
[TD]
4150​
[/TD]
[TD]
4160​
[/TD]
[TD][/TD]
[TD]
-167.29​
[/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]
1/2/2016​
[/TD]
[TD]
2201​
[/TD]
[TD]
14​
[/TD]
[TD]
0​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Other Sales[/TD]
[TD]
4900​
[/TD]
[TD]
4910​
[/TD]
[TD][/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]
2/5/2016​
[/TD]
[TD]
2100​
[/TD]
[TD]
15​
[/TD]
[TD]
-82.89​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]NET SALES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD]
1/2/2016​
[/TD]
[TD]
7100​
[/TD]
[TD]
7​
[/TD]
[TD]
-2084​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
12​
[/TD]
[TD]
1/17/2016​
[/TD]
[TD]
4000​
[/TD]
[TD]
1​
[/TD]
[TD]
-55.9​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
13​
[/TD]
[TD]
1/2/2016​
[/TD]
[TD]
4900​
[/TD]
[TD]
7​
[/TD]
[TD]
-800​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
14​
[/TD]
[TD]
2/20/2016​
[/TD]
[TD]
4024​
[/TD]
[TD]
1​
[/TD]
[TD]
800​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
15​
[/TD]
[TD]
1/2/2016​
[/TD]
[TD]
2201​
[/TD]
[TD]
2​
[/TD]
[TD]
0​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
16​
[/TD]
[TD]
1/2/2016​
[/TD]
[TD]
4900​
[/TD]
[TD]
7​
[/TD]
[TD]
167.29​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
17​
[/TD]
[TD]
1/31/2016​
[/TD]
[TD]
4155​
[/TD]
[TD]
1​
[/TD]
[TD]
-167.29​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
18​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 896"]
<tbody>[TR]
[TD="colspan: 14"] 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[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
More efficient...

Sheet1

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][/tr]
[tr][td]
1​
[/td][td] DATE[/td][td] NOMINAL_CODE[/td][td] DEPT_NUMBER[/td][td] AMOUNT[/td][/tr]


[tr][td]
2​
[/td][td]
1/1/2016​
[/td][td] 4000[/td][td] 12[/td][td]
0​
[/td][/tr]


[tr][td]
3​
[/td][td]
1/2/2016​
[/td][td] 7100[/td][td] 1[/td][td]
3000​
[/td][/tr]


[tr][td]
4​
[/td][td]
1/2/2016​
[/td][td] 2100[/td][td] 12[/td][td]
-3000​
[/td][/tr]


[tr][td]
5​
[/td][td]
1/2/2016​
[/td][td] 1353[/td][td] 0[/td][td]
-800​
[/td][/tr]


[tr][td]
6​
[/td][td]
1/2/2016​
[/td][td] 4003[/td][td] 1[/td][td]
800​
[/td][/tr]


[tr][td]
7​
[/td][td]
1/2/2016​
[/td][td] 1353[/td][td] 0[/td][td]
-1415.42​
[/td][/tr]


[tr][td]
8​
[/td][td]
1/2/2016​
[/td][td] 7150[/td][td] 14[/td][td]
83​
[/td][/tr]


[tr][td]
9​
[/td][td]
1/2/2016​
[/td][td] 2201[/td][td] 14[/td][td]
0​
[/td][/tr]


[tr][td]
10​
[/td][td]
2/5/2016​
[/td][td] 2100[/td][td] 15[/td][td]
-82.89​
[/td][/tr]


[tr][td]
11​
[/td][td]
1/2/2016​
[/td][td] 7100[/td][td] 7[/td][td]
-2084​
[/td][/tr]


[tr][td]
12​
[/td][td]
1/17/2016​
[/td][td] 4000[/td][td] 1[/td][td]
-55.9​
[/td][/tr]


[tr][td]
13​
[/td][td]
1/2/2016​
[/td][td] 4900[/td][td] 7[/td][td]
-800​
[/td][/tr]


[tr][td]
14​
[/td][td]
2/20/2016​
[/td][td] 4024[/td][td] 1[/td][td]
800​
[/td][/tr]


[tr][td]
15​
[/td][td]
1/2/2016​
[/td][td] 2201[/td][td] 2[/td][td]
0​
[/td][/tr]


[tr][td]
16​
[/td][td]
1/2/2016​
[/td][td] 4900[/td][td] 7[/td][td]
167.29​
[/td][/tr]


[tr][td]
17​
[/td][td]
1/31/2016​
[/td][td] 4155[/td][td] 1[/td][td]
-167.29​
[/td][/tr]
[/table]

Sheet2

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][/tr]
[tr][td]
1​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] Dept[/td][td] [/td][/tr]


[tr][td]
2​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] 1[/td][td] [/td][/tr]


[tr][td]
3​
[/td][td] GPM[/td][td] [/td][td] [/td][td] [/td][td] 40.50%[/td][td] [/td][/tr]


[tr][td]
4​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] RESULTS I WANT[/td][td] [/td][/tr]


[tr][td]
5​
[/td][td] SALES[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]
6​
[/td][td] Sales[/td][td]
4000​
[/td][td]
4003​
[/td][td] [/td][td]
744.1​
[/td][td] [/td][/tr]


[tr][td]
7​
[/td][td] Consult Sales[/td][td]
4024​
[/td][td]
4026​
[/td][td] [/td][td]
800​
[/td][td] [/td][/tr]


[tr][td]
8​
[/td][td] Less Consults[/td][td]
4150​
[/td][td]
4160​
[/td][td] [/td][td]
-167.29​
[/td][td] [/td][/tr]


[tr][td]
9​
[/td][td] Other Sales[/td][td]
4900​
[/td][td]
4910​
[/td][td] [/td][td]
0​
[/td][td] [/td][/tr]


[tr][td]
10​
[/td][td] NET SALES[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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>
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
transpose data int listbox on userform based on two dates or without dates
Replies
2
Views
320
Alaa mg
A
L
  • Solved
SUMIFS based on multiple criteria and between date
2
Replies
17
Views
841
etaf
etaf
A
  • Solved
split range into multiple range based on blank row for each range
Replies
4
Views
508
Ali M
A
A
  • Question Question
I need get output using excel formuals
Replies
3
Views
666
Fluff
Fluff
O
  • Question Question
Survey data to capture full month
Replies
5
Views
875
DanteAmor
DanteAmor

Forum statistics

Threads
1,222,900
Messages
6,168,926
Members
452,227
Latest member
sam1121

Share this page

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