SUMIFS with UNIQUE MULTIPLE search...

proficient

proficient

Well-known Member
Joined
Apr 10, 2012
Messages
716
Office Version
2016
Platform
Windows, 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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Robert Mika

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
proficient said:
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
Click to expand...
That's easy job for PivotTable..
 
proficient

proficient

Well-known Member
Joined
Apr 10, 2012
Messages
716
Office Version
2016
Platform
Windows, 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 :)
 
C

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

proficient

Well-known Member
Joined
Apr 10, 2012
Messages
716
Office Version
2016
Platform
Windows, Mobile
Charlie45 said:
In cell E2 enter:
=SUMIFS(Sheet1!D:D,Sheet1!A:A,A2,Sheet1!B:B,B2,Sheet1!C:C,C2)

Copy down column
Click to expand...
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

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
proficient said:
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 :)
Click to expand...
The are not unique item per one columns- there re unique with condition that's tricky.
I recommed PivotTable:
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Job #</td><td style="text-align: center;;">Wash</td><td style="text-align: center;;">Qty</td><td style="text-align: center;;">Sum`</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">7781</td><td style="text-align: center;;">DK</td><td style="text-align: center;;">1250</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">7785</td><td style="text-align: center;;">DK</td><td style="text-align: center;;">1400</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">3</td><td style="text-align: center;;">7862</td><td style="text-align: center;;">LT</td><td style="text-align: center;;">1550</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">2</td><td style="text-align: center;;">7779</td><td style="text-align: center;;">Blue</td><td style="text-align: center;;">1700</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Job #</td><td style=";">Wash</td><td style=";">Qty</td><td style=";">Sum of Sum`</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">2</td><td style="text-align: center;;">7797</td><td style="text-align: center;;">Blue</td><td style="text-align: center;;">1850</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">7781</td><td style=";">DK</td><td style="text-align: right;;">5400</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">1</td><td style="text-align: center;;">7781</td><td style="text-align: center;;">DK</td><td style="text-align: center;;">2000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">7785</td><td style=";">DK</td><td style="text-align: right;;">3850</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">1</td><td style="text-align: center;;">7781</td><td style="text-align: center;;">DK</td><td style="text-align: center;;">2150</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">7779</td><td style=";">Blue</td><td style="text-align: right;;">4300</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">2</td><td style="text-align: center;;">7841</td><td style="text-align: center;;">LT</td><td style="text-align: center;;">2300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">7797</td><td style=";">Blue</td><td style="text-align: right;;">4600</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">1</td><td style="text-align: center;;">7785</td><td style="text-align: center;;">DK</td><td style="text-align: center;;">2450</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">7841</td><td style=";">LT</td><td style="text-align: right;;">2300</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">2</td><td style="text-align: center;;">7779</td><td style="text-align: center;;">Blue</td><td style="text-align: center;;">2600</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;">7797</td><td style=";">LK01</td><td style="text-align: right;;">4150</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">2</td><td style="text-align: center;;">7797</td><td style="text-align: center;;">Blue</td><td style="text-align: center;;">2750</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;">7862</td><td style=";">LT</td><td style="text-align: right;;">3550</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">3</td><td style="text-align: center;;">7862</td><td style="text-align: center;;">LT</td><td style="text-align: center;;">2000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">3</td><td style="text-align: center;;">7797</td><td style="text-align: center;;">LK01</td><td style="text-align: center;;">4150</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet3</p><br /><br />


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

proficient

Well-known Member
Joined
Apr 10, 2012
Messages
716
Office Version
2016
Platform
Windows, Mobile
Robert Mika said:
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.
Click to expand...
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... :)
 
You must log in or register to reply here.

Watch MrExcel Video

Learn Excel 2010 - &quot;Next Invoice Number&quot;: Podcast #1505

Forum statistics

Threads
1,095,165
Messages
5,442,782
Members
405,196
Latest member
rmuir12

Share this page

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    • Started by AndyEd
    • Replies: 29
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    • Started by omairhe
    • Replies: 26
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    • Started by Marwan69
    • Replies: 24
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    • Started by Burrgogi
    • Replies: 22
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top