SUMIFS with UNIQUE MULTIPLE search...

proficient

Well-known Member
Joined
Apr 10, 2012
Messages
726
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
Assalam-o-alaikum and Hi every one... :)

What I want is that here is my sample data with desire result, I want to search UNIQUE Job and Wash and then the total qty. for your reference I made a dummy sheet with desire result, on SHEET 1 I have the DATA and on sheet 2 I want the result... is it possible?

Sheet1

*ABCD
1SHEET 1**
2UnitJob #WashQty
317781DK1250
417785DK1400
537862LT1550
627779Blue1700
727797Blue1850
817781DK2000
917781DK2150
1027841LT2300
1117785DK2450
1227779Blue2600
1327797Blue2750
1437862LT2000
1537797LK014150
16****
17SHEET 2**
18UnitJob #WashQty
1917781DK5400
2017785DK3850
2127779Blue4300
2227797Blue4600
2327841LT2300
2437862LT3550
2537797LK014150

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
Assalam-o-alaikum and Hi every one... :)

What I want is that here is my sample data with desire result, I want to search UNIQUE Job and Wash and then the total qty. for your reference I made a dummy sheet with desire result, on SHEET 1 I have the DATA and on sheet 2 I want the result... is it possible?

Sheet1

*
A
B
C
D
1
SHEET 1
*
*
2
Unit
Job #
Wash
Qty
3
1
7781
DK
1250
4
1
7785
DK
1400
5
3
7862
LT
1550
6
2
7779
Blue
1700
7
2
7797
Blue
1850
8
1
7781
DK
2000
9
1
7781
DK
2150
10
2
7841
LT
2300
11
1
7785
DK
2450
12
2
7779
Blue
2600
13
2
7797
Blue
2750
14
3
7862
LT
2000
15
3
7797
LK01
4150
16
*
*
*
*
17
SHEET 2
*
*
18
Unit
Job #
Wash
Qty
19
1
7781
DK
5400
20
1
7785
DK
3850
21
2
7779
Blue
4300
22
2
7797
Blue
4600
23
2
7841
LT
2300
24
3
7862
LT
3550
25
3
7797
LK01
4150

<TBODY>
</TBODY>


Excel tables to the web >> Excel Jeanie HTML 4
That's easy job for PivotTable..
 

proficient

Well-known Member
Joined
Apr 10, 2012
Messages
726
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
But Robert I never used pivot table actually... can you tell me how can I do this by Pivot table and I'll more appreciate if you give me the formula I want... I think you should give me the both :)
 

Charlie45

Board Regular
Joined
Dec 13, 2003
Messages
225
In cell E2 enter:
=SUMIFS(Sheet1!D:D,Sheet1!A:A,A2,Sheet1!B:B,B2,Sheet1!C:C,C2)

Copy down column
 

proficient

Well-known Member
Joined
Apr 10, 2012
Messages
726
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

In cell E2 enter:
=SUMIFS(Sheet1!D:D,Sheet1!A:A,A2,Sheet1!B:B,B2,Sheet1!C:C,C2)

Copy down column

Thanks for your reply CHARLIE45 but I want all columns in sheet 2 without giving any input there, the formula should found all unique values by it self, the problem is that here are many job which has same washes, that is why sheet is more complicated for me... Hope making sense...
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
But Robert I never used pivot table actually... can you tell me how can I do this by Pivot table and I'll more appreciate if you give me the formula I want... I think you should give me the both :)

The are not unique item per one columns- there re unique with condition that's tricky.
I recommed PivotTable:

Excel 2010
ABCDEFGHIJK
1Job #WashQtySum`
217781DK1250
317785DK1400
437862LT1550
527779Blue1700Job #WashQtySum of Sum`
627797Blue185017781DK5400
717781DK200017785DK3850
817781DK215027779Blue4300
927841LT230027797Blue4600
1017785DK245027841LT2300
1127779Blue260037797LK014150
1227797Blue275037862LT3550
1337862LT2000
1437797LK014150
15
16
17
Sheet3



Example
https://dl.dropbox.com/u/78583609/Pivot.xlsx
or go to
How to extract a unique distinct list from a column in excel | Get Digital Help - Microsoft Excel resource

to play with Oscars formulas.
 

proficient

Well-known Member
Joined
Apr 10, 2012
Messages
726
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
The are not unique item per one columns- there re unique with condition that's tricky.
I recommed PivotTable:
Excel 2010
ABCDEFGHIJK
1Job #WashQtySum`
217781DK1250
317785DK1400
437862LT1550
527779Blue1700Job #WashQtySum of Sum`
627797Blue185017781DK5400
717781DK200017785DK3850
817781DK215027779Blue4300
927841LT230027797Blue4600
1017785DK245027841LT2300
1127779Blue260037797LK014150
1227797Blue275037862LT3550
1337862LT2000
1437797LK014150
15
16
17

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3




Example
https://dl.dropbox.com/u/78583609/Pivot.xlsx
or go to
How to extract a unique distinct list from a column in excel | Get Digital Help - Microsoft Excel resource

to play with Oscars formulas.

Thanks for your response Robert, I have visited the web site you given, I found
[h=2]Extract a unique distinct list from three columns,[/h]But this is not what I want, but I will try Pivot Table and again thanks for your response... :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,111
Messages
5,835,444
Members
430,356
Latest member
RadStorm20

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