An alternate approach...

Say your headings are in rows 1:2.
Insert a new row 1.

Put this in A1:
=subtotal(3,A4:a65536)

put this in B1:
=subtotal(9,A4:a65536)

=subtotal(3,...) counts the number of entries you can see in that range
=subtotal(9,...) sums the quantities in the cells you can see.

Now select A3 (last row of headers) through the bottom right cell of your data.

Click on Data|filter|autofilter

Now filter column A by one of your employee id's.

Those formulas will reflect the values for just those visible cells.

(I'd add one more column that summed all the states in each row. Just to make
it a little easier to get a grand total.)




wrote:

I have been working on this one for an hour or so. Any help would be
greatly appreciated.

Unfortunately, I am not good with Match and Index, most likely both of
which would need to be used in the example below.

Here is my Data (numbers under state represent units sold in each
state):

Emp ID State
. CA NY TX FL IA
1045 10 10 0 40 70
1046 20 80 40 70 90
1047 0 0 60 50 30
1048 50 50 10 20 20
1049 60 20 80 10 0

I would like to set up a formula by which a person can type in an
employee ID and retrieve all the sales data for that employee.

I envision it listing out each state and corresponding units sold for
that employee. I would like the output to be sorted by units sold
highest to lowest.

I have used a combination of Index, Match and Large functions to get
what I want for a known employee number but that doesn't work if I have
an unknown employee (prior to user input). I am assuming a lookup
would need to be nested in there somewhere. I am just having trouble
figuring out the order. Then again, Maybe it I am missing the boat
completely.

Also, if anyone has any suggestions on breaking ties in rankings using
the LARGE function, I could use some advice there also.
Thanks in advance

--

Dave Peterson