How to use SUMIF & VLOOKUP on filtered results

[megamanx85x]

Hi there, I started a new job in which I have been tasked to filter out an excel sheet that logs all the jobs that were inspected everyday. It has multiple columns in which the critical data is filtering out the defect code and the quantity associated with it.

Originally I managed to make a formula in this mannner: =SUMIF('NAME OF SHEET'!K:K, VLOOKUP(A28,'NAME OF SHEET'!K:L,1,FALSE),'NAME OF SHEET'!L:L)

This worked fine as the entire two columns K and L would total up all the defect codes in relations to "A#" and etc. However this was a setback on my part as I need the sheet that sums up all the defect codes to be categorized by item #, there are more then thirty item #'s I can filter in the column and when I attempt to use the same formula it still grabs all the data in the two columns.

So now I tried to make a new formula that would only focus on the filtered data after the item # has been selected, and it hasn't worked obviously since I'm here; it looks like this:

=VLOOKUP(U2,IF(SUBTOTAL(3,OFFSET(K2:K100,ROW(K2:K100)-ROW(K2),0,1))>0,K2:L100)2,0)

I'm sure many of you will see what didn't go wrong but I am aware of the SUBTOTAL command 3 is not for SUM, this is the only option that works the way I have it set up and its only COUNTA which isn't what I need. If I attempt to use either 9 or 109 the formula errors out.

Its critical that I have this formula be on the second sheet and reference the original sheet location for columns/row K & L, please if anyone can help in correcting me on how to make sure it grabs only filtered data and SUMS it all up I would greatly appreciate it.

Also Merry Christmas!

 

[hernantorres23]

Hi, we are pleased to help you, but please, put your data example, using XL2BB.
Note: If would necessary, pay attention to this post XL2BB 2 Square

 

[megamanx85x]

Hi, we are pleased to help you, but please, put your data example, using XL2BB.
Note: If would necessary, pay attention to this post XL2BB 2 Square

I can't really use that feature on my work computer because i dont have permission to install additional software, nor can I make a copy of the excel sheet because of the high security we have in transferring files or emailing them. Sorry I wasn't aware of this.

 

[hernantorres23]

Ok, but paste HTML with some values, you can simulate an escenario (Excel table)

 

[MARK858]

@megamanx85x, make a mock up of your data, make sure that your layout is exactly the same as in the original document.
Now just select and copy your range and (make sure the cog symbol at the top of the reply window is black and white, if it is orange click it to toggle it back) paste it in the thread .

It should post something like the below unless you are using Edge or Safari, which although not ideal it is usable.

Order ID	Product Name
106-5-N21	Teatime Chocolate Biscuits
109-19-F61	Filo Mix
103-23-F90	Spegesild
106-18-F04	Lakkalikööri
104-17-F18	Zaanse koeken
109-17-F54	Pavlova
106-11-N65	Manjimup Dried Apples
106-25-N82	Rhönbräu Klosterbier
105-30-F22	Northwoods Cranberry Sauce
106-19-F37	Gnocchi di nonna Alice
103-26-F95	Spegesild
108-14-N32	Gula Malacca
109-26-N78	Northwoods Cranberry Sauce
103-16-N06	Tourtière
107-22-N89	Scottish Longbreads
105-9-N32	Nord-Ost Matjeshering
109-5-F28	Lakkalikööri
107-4-N28	Nord-Ost Matjeshering
106-28-N12	Inlagd Sill
104-12-F72	Manjimup Dried Apples

 

[megamanx85x]

Here is a basic example of my data sheet, I omitted the other columns cause I dont have the information on them right now but these are the main three i am using. I filter the data with Item# so I can focus on the defect code and the scrap total for that defect code.

Defect Code and Scrap are columns K & L per my formulas.

Every time I change the item# in the drop down filter the defect codes will change and I need the formula to display the results shown only from the filter and not everything in the entire columns.

 

[hernantorres23]

Thats is the result are you looking for? ?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	item	colB	colC	colD	colE	colF	colG	colH	colI	colJ	defect_code	scrap
2	45001										27-scratches	15
3	45001										03-tears	45
4	45001										27-scratches	9
5	45001										27-scratches	19			item	defect_code	Sum of scrap
6	45001										27-scratches	33			45001	03-tears	45
7	45001										27-scratches	10				27-scratches	76
8															45001 Total		121
9															Grand Total		121
10
11
12
Sheet2

 

[megamanx85x]

That’s pretty much what I needed it to do, was to sum up all defect codes in a filtered result only. Fans total isn’t necessary, I have a second sheet where the formula is applied to each defect code hence the vlookup formula is set to search each defect code in the main sheet and add them all.

 

[hernantorres23]

Data in blue highlight is a Pivot table, and you can move this table, where you want.

See full example here

 

[megamanx85x]

Is this using the original formulas I had before or just a whole new pivot table? Can I possible expand the pivot table to hold all 100 defect codes!