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]

Yes you can, 100, 500, 1000