sumproduct(vlookup,vlookup)

[condatis]

Ok, so basically i want to do what it says in the title. I have two tables that i need to lookup the array for the two given items and take the sumproduct. vlookup obviously doesn't work with arrays so I'm not sure how to do this. Let's say there are two tables, 200 rows by 10 columns each. Table one i want to look up the row that has 001 as the key, table two i want to look up the row that has SUB as the key, and take the sum product. Any suggestions?

 

[Aladin Akyurek]

Ok, so basically i want to do what it says in the title. I have two tables that i need to lookup the array for the two given items and take the sumproduct. vlookup obviously doesn't work with arrays so I'm not sure how to do this. Let's say there are two tables, 200 rows by 10 columns each. Table one i want to look up the row that has 001 as the key, table two i want to look up the row that has SUB as the key, and take the sum product. Any suggestions?

Assuming that you actually want a count...

=SUMPRODUCT(--(A2:A100="001"),--($B$2:$B$100="SUB"))

Adjust the ranges to suit.

If this is not what you have in mind, try to elaborate on the objective.

 

[barry houdini]

If your "key" is in column A and the data in B to J then you could use this formula

=SUMPRODUCT(INDEX(B1:J100,MATCH("001",A1:A100,0),0),INDEX(B1:J100,MATCH("Sub",A1:A100,0),0))

I'm assuming that 001 is text formatted......

 

[condatis]

If your "key" is in column A and the data in B to J then you could use this formula

=SUMPRODUCT(INDEX(B1:J100,MATCH("001",A1:A100,0),0),INDEX(B1:J100,MATCH("Sub",A1:A100,0),0))

I'm assuming that 001 is text formatted......

Barry you da man. This is exactly what i needed. I someone would have an easier way to do it than i was thinking about.

 

[condatis]

Ok, here's the next part of this. The key that I am matching the first colunm of one of the tables to is a dropdown menu, the other one is static. I have a simple macro that autofilters out anything that turns up zero. How do i make it so this macro automatically runs when i select something new on the dropdown list?

 

[Aladin Akyurek]

Ok, here's the next part of this. The key that I am matching the first colunm of one of the tables to is a dropdown menu, the other one is static. I have a simple macro that autofilters out anything that turns up zero. How do i make it so this macro automatically runs when i select something new on the dropdown list?

Sorry for the incomplete reading of your request on the first round. However, would you elaborate on why you need autofiltering?

 

[condatis]

Sorry for the incomplete reading of your request on the first round. However, would you elaborate on why you need autofiltering?

I need autofiltering because i want to hide all the rows that come up 0. Essentially, i have locations, parts (qtys), and assemblies (qtys).

So i have one table with locations as the key, assemblies across the top. The other with part#s as the key and assemblies across the top. So for instance at location 1 i have assembly a, 2 b's and one c. In assembly A i these parts and the qtys of those parts. B, C, ect. So the sumproduct tells me that at location 1 i have these parts.

When I pull up a certain location # i want to see what parts it has, but I don't want to be bothered with a bunch of rows that have 0 qty on them. Right now i have it as a macro tied to CTRL+L, but I'd like it automatic on the change in location.

So anyway, the macro is made and named, just not tied to auto execute upon changing the location #.