SUMIFS with UNIQUE MULTIPLE search...

[proficient]

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

<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

 

[Robert Mika]

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]

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]

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

Copy down column

 

[proficient]

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]

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
	A	B	C	D	E	F	G	H	I	J	K
1	Job #	Wash	Qty	Sum`
2	1	7781	DK	1250
3	1	7785	DK	1400
4	3	7862	LT	1550
5	2	7779	Blue	1700				Job #	Wash	Qty	Sum of Sum`
6	2	7797	Blue	1850				1	7781	DK	5400
7	1	7781	DK	2000				1	7785	DK	3850
8	1	7781	DK	2150				2	7779	Blue	4300
9	2	7841	LT	2300				2	7797	Blue	4600
10	1	7785	DK	2450				2	7841	LT	2300
11	2	7779	Blue	2600				3	7797	LK01	4150
12	2	7797	Blue	2750				3	7862	LT	3550
13	3	7862	LT	2000
14	3	7797	LK01	4150
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]

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

	A	B	C	D	E	F	G	H	I	J	K
1	Job #	Wash	Qty	Sum`
2	1	7781	DK	1250
3	1	7785	DK	1400
4	3	7862	LT	1550
5	2	7779	Blue	1700				Job #	Wash	Qty	Sum of Sum`
6	2	7797	Blue	1850				1	7781	DK	5400
7	1	7781	DK	2000				1	7785	DK	3850
8	1	7781	DK	2150				2	7779	Blue	4300
9	2	7841	LT	2300				2	7797	Blue	4600
10	1	7785	DK	2450				2	7841	LT	2300
11	2	7779	Blue	2600				3	7797	LK01	4150
12	2	7797	Blue	2750				3	7862	LT	3550
13	3	7862	LT	2000
14	3	7797	LK01	4150
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...

 

[Robert Mika]

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 are welcome