Need assistance with Excel search and count query with filtered cells

Hello,


I am trying to create a summary sheet that details the amount of a certain product number found in a separate tab.  The inventory tab has around 33k records that are filtered by certain manager names.

I need to search by the first four numbers on the inventory tab to match column A on the summary sheet.

Example:  The summary sheet may list the model type as 0446, but the inventory sheet lists it as 04463AW3234.

Model Type Codename Marketing Name Quantity
0446 Codename 1 Product 1 0
0606 Codename 2 Product 2 0
0792 Codename 3 Product 3 0


I would add a formula into column D cells (quantity) that takes the text from cells within column A (model type) searches that through the inventory tab, and lists the quantity of that model type.

This is the formula I have so far, but it detects all cells with 0446 anywhere in the test.  I need to edit it where it will only search for the first four numbers taken from column A.

=SUMPRODUCT(SUBTOTAL(103,OFFSET('Development-Asset list'!G2:G33080,ROW('Development-Asset list'!G2:G33080)-MIN(ROW('Development-Asset list'!G2:G33080)),,1)),ISNUMBER(SEARCH(B5,'Development-Asset list'!G2:G33080))+0)

Any help would be appreciated!

April 7th, 2015 10:32am

Not sure if you meant column B or column A for the source of the 0446 - your formula seems like B

=SUMPRODUCT(SUBTOTAL(103,OFFSET('Development-Asset list'!G2:G33080,ROW('Development-Asset list'!G2:G33080)-MIN(ROW('Development-Asset list'!G2:G33080)),,1)),(LEFT('Development-Asset list'!G2:G33080,4)=B5)+0)

Change the B5 to the actual cell with the 0446 string


Free Windows Admin Tool Kit Click here and download it now
April 7th, 2015 1:39pm

Thank you that worked!
April 7th, 2015 2:31pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics