SUMIFS with UNIQUE MULTIPLE search...

proficient

proficient

Well-known Member
Joined
Apr 10, 2012
Messages
745
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
**
****
**

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="colspan: 2, align: center"]SHEET 1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]Unit[/TD]
[TD="align: center"]Job #[/TD]
[TD="align: center"]Wash[/TD]
[TD="align: center"]Qty[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7781[/TD]
[TD="align: center"]DK[/TD]
[TD="align: center"]1250[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7785[/TD]
[TD="align: center"]DK[/TD]
[TD="align: center"]1400[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]7862[/TD]
[TD="align: center"]LT[/TD]
[TD="align: center"]1550[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7779[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]1700[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7797[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]1850[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7781[/TD]
[TD="align: center"]DK[/TD]
[TD="align: center"]2000[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7781[/TD]
[TD="align: center"]DK[/TD]
[TD="align: center"]2150[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7841[/TD]
[TD="align: center"]LT[/TD]
[TD="align: center"]2300[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7785[/TD]
[TD="align: center"]DK[/TD]
[TD="align: center"]2450[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7779[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]2600[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7797[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]2750[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]7862[/TD]
[TD="align: center"]LT[/TD]
[TD="align: center"]2000[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]7797[/TD]
[TD="align: center"]LK01[/TD]
[TD="align: center"]4150[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="colspan: 2, align: center"]SHEET 2[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: center"]Unit[/TD]
[TD="align: center"]Job #[/TD]
[TD="align: center"]Wash[/TD]
[TD="align: center"]Qty[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7781[/TD]
[TD="align: center"]DK[/TD]
[TD="align: center"]5400[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7785[/TD]
[TD="align: center"]DK[/TD]
[TD="align: center"]3850[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7779[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]4300[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7797[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]4600[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7841[/TD]
[TD="align: center"]LT[/TD]
[TD="align: center"]2300[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]7862[/TD]
[TD="align: center"]LT[/TD]
[TD="align: center"]3550[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]7797[/TD]
[TD="align: center"]LK01[/TD]
[TD="align: center"]4150[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Sort by date Sort by votes
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
*
*
Unit
Job #
Wash
Qty
1
7781
DK
1250
1
7785
DK
1400
3
7862
LT
1550
2
7779
Blue
1700
2
7797
Blue
1850
1
7781
DK
2000
1
7781
DK
2150
2
7841
LT
2300
1
7785
DK
2450
2
7779
Blue
2600
2
7797
Blue
2750
3
7862
LT
2000
3
7797
LK01
4150
*
*
*
*
*
*
Unit
Job #
Wash
Qty
1
7781
DK
5400
1
7785
DK
3850
2
7779
Blue
4300
2
7797
Blue
4600
2
7841
LT
2300
3
7862
LT
3550
3
7797
LK01
4150

<TBODY>
[TD="bgcolor: #cacaca"]1
[/TD]
[TD="colspan: 2"]SHEET 1
[/TD]

[TD="bgcolor: #cacaca"]2
[/TD]

[TD="bgcolor: #cacaca"]3
[/TD]

[TD="bgcolor: #cacaca"]4
[/TD]

[TD="bgcolor: #cacaca"]5
[/TD]

[TD="bgcolor: #cacaca"]6
[/TD]

[TD="bgcolor: #cacaca"]7
[/TD]

[TD="bgcolor: #cacaca"]8
[/TD]

[TD="bgcolor: #cacaca"]9
[/TD]

[TD="bgcolor: #cacaca"]10
[/TD]

[TD="bgcolor: #cacaca"]11
[/TD]

[TD="bgcolor: #cacaca"]12
[/TD]

[TD="bgcolor: #cacaca"]13
[/TD]

[TD="bgcolor: #cacaca"]14
[/TD]

[TD="bgcolor: #cacaca"]15
[/TD]

[TD="bgcolor: #cacaca"]16
[/TD]

[TD="bgcolor: #cacaca"]17
[/TD]
[TD="colspan: 2"]SHEET 2
[/TD]

[TD="bgcolor: #cacaca"]18
[/TD]

[TD="bgcolor: #cacaca"]19
[/TD]

[TD="bgcolor: #cacaca"]20
[/TD]

[TD="bgcolor: #cacaca"]21
[/TD]

[TD="bgcolor: #cacaca"]22
[/TD]

[TD="bgcolor: #cacaca"]23
[/TD]

[TD="bgcolor: #cacaca"]24
[/TD]

[TD="bgcolor: #cacaca"]25
[/TD]

</TBODY>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...
That's easy job for PivotTable..
 
Upvote 0
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 :)
 
Upvote 0
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...
 
Upvote 0
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:

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.
 
Upvote 0
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
Job #WashQtySum of Sum`
DK
DK
Blue
Blue
LT
LK01
LT

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Job #[/TD]
[TD="align: center"]Wash[/TD]
[TD="align: center"]Qty[/TD]
[TD="align: center"]Sum`[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7781[/TD]
[TD="align: center"]DK[/TD]
[TD="align: center"]1250[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7785[/TD]
[TD="align: center"]DK[/TD]
[TD="align: center"]1400[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]7862[/TD]
[TD="align: center"]LT[/TD]
[TD="align: center"]1550[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7779[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]1700[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7797[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]1850[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7781[/TD]

[TD="align: right"]5400[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7781[/TD]
[TD="align: center"]DK[/TD]
[TD="align: center"]2000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7785[/TD]

[TD="align: right"]3850[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7781[/TD]
[TD="align: center"]DK[/TD]
[TD="align: center"]2150[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7779[/TD]

[TD="align: right"]4300[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7841[/TD]
[TD="align: center"]LT[/TD]
[TD="align: center"]2300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7797[/TD]

[TD="align: right"]4600[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7785[/TD]
[TD="align: center"]DK[/TD]
[TD="align: center"]2450[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7841[/TD]

[TD="align: right"]2300[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7779[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]2600[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7797[/TD]

[TD="align: right"]4150[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7797[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]2750[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7862[/TD]

[TD="align: right"]3550[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]7862[/TD]
[TD="align: center"]LT[/TD]
[TD="align: center"]2000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]7797[/TD]
[TD="align: center"]LK01[/TD]
[TD="align: center"]4150[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

Similar threads

M
  • Solved
Help with separating Text String from a Excel Col
Replies
3
Views
647
Peter_SSs
Peter_SSs
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
willow1985
  • Solved
Help with SUMIFS with multiple conditions
Replies
9
Views
2K
Fluff
Fluff
H
  • Question Question
Get Nearest value from a range based on a reference cell value
Replies
5
Views
957
hsandeep
H
H
  • Question Question
comparing values in a column against multiple sheets and returning subsequent values from other columns
Replies
1
Views
1K
DanteAmor
DanteAmor

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