Lookup functions

[haas786]

Hello all,

I have a table which contains quite a few entries. What I am trying to
do is get a number (from this table) based on 3 different criterias.
For example, given that a person is in "Group 2", and the name of that
person is "Larry", what are his sales figures for "Tuesday"? Just to be
clear, Larry can be in more than one group, and have sales figures for
every day. The table from which we get this info has the following as
fields: 'GroupNumber", "Sale Rep Name", "Monday", "Tuesday",
Wednesday", Thursday", and "Friday"...the days represent sales figures.

I know I can get answers based on two criteria using Index and Match
functions...how about for 3 or more?

Thanks for any and all help!

 

[SteveG]

If you are using the Monday, Tuesday etc... as labels in your
destination cells, you don't have to use it as a criteria. So for
instance, if where you are rolling up this data, you have the same
headers Group Number, Sale Rep Name and then the days of the week in
A1:G1. Under the Monday header for Larry in Group 1 use:

=SUMPRODUCT((YourDataSheet$A$2:$A$10=$A2)*(YourDataSheet$B$2:$B$13=$B2),(YourDataSheet$C$2:$C$10))

Copy this down your list of group and rep names.


Does that Help?

Steve

 

[SteveG]

A slight change.

=SUMPRODUCT((YourDataSheet$A$2:$A$10=$A2)*(YourDataSheet$B$2:$B$13=$B2)*(YourDataSheetC$2:C$10))

This way when you copy accross for each day, the references will move
with it correctly.

Steve