Help with complex table lookup

[Zachary Turner]

I have a ranged defined in a spreadsheet that consists of a bunch of
values. Some of the cells in the range may be empty.

I also have 4 hidden lookup tables at the bottom of my spreadsheet.
Using these 4 lookup tables, I know how to use the vector form of the
LOOKUP function to determine the following individually:

- Given the value in one cell, what is the corresponding
"route" (lookup from table 1)
- Given the value in one cell, what is the corresponding "set" (lookup
from table 2)
- Given the value in one cell, what is the corresponding
"slot" (lookup from table 3)
- Given the value in one cell, what is the corresponding
"level" (lookup from table 4)

What I would like to be able to determine is:

Given the entire range of cells, how many values have route=W, set=X,
slot=Y, and level=Z.

Is there a way to do this without macros, using only normal
expressions and standard excel functions?

Thanks

 

[Guest]

The sumproduct() function will do exactly what you want.