How to embed lookup function from a dropdown list within SUMIFS

[exlstudent]

Cell I16 is a dropdown box containing not only names for individual salesperson but also the various groupings, such as Male/Female, Product Line, Region, etc.

The question is: how to summarize monthly sales data based on the value selected from the droplist without adding multiple cross reference columns to the data block itself?

Thanks!

[DBY]

Hello
If I've understood you correctly, try:

Please Login or Register  to view this content.

copied across from cell J16.

Hope this helps.
DBY

[alansidman]

Here is a table solution using Sumifs that allows for you change the criteria you are looking up dynamically.

Alan

[exlstudent]

Hi DBY,

Very nice solution. Never thought about that.

But what if the data block is much bigger/longer than the sales person cross reference and what if different values in the cross reference table aren't unique?

See the modified data sheet.

Thanks again!

exlstudent

[DBY]

I probably haven't understood your question correctly. My solution was based on a comparison between the two tables. The way you now have it there is no relationship between the Reference table and the Sales Data apart from the Sales Person name, so I don't see how it can be done unless you add extra columns to the Sales Data table or expand the Reference table to match the Sales Data table.

Sorry DBY

[alansidman]

Did you look at my solution? It can incorporate the additional criteria.
Alan