Searching data in multiple rows using search criteria

[bletch23]

Hi,

I'm trying to create spreadsheet that utilizes data in two different rows.
The spreadsheet is meant to track types of accounts assigned to a number of
associates I work with.

The first row logs the name of the employee who receives the account. To the
right of this in the second row is logged the type of account received, as an
example we could use 'simple', 'medium' and 'hard'. With this in mind, the
table could look like something like this:

Matt > Simple
Matt > Medium
Jennifer > Simple
Jennifer > Hard
Michael > Hard
Matt > Simple
Jennifer > Medium, etc.

My question is, how do I generate a function that not only:
1. Logs the number of accounts assigned to each employee
2. But also has three columns which log the type of account (simple, medium,
or hard)

I'm having a difficult time finding a formula that will search for the
employee name, check the type of value on the row to the right, and then log
the data. Any ideas? Any help would be greatly appreciated!

 

[Ashish Mathur]

Hi,

Create a pivot table. Drag Name to the row area and Account to the column
area. In the data area, drop the account (once again).

Hope this helps.
--
Regards,

Ashish Mathur
Excel MVP
(www.ashishmathur.com)

 

[Max]

Personally I'd go with the pivot approach explained by Ashish ...
For the formulas approach, you could use sumproduct to populate the desired
counts
Assume source table in Sheet1, cols A and B, data from row2 down, col A =
names, col B = accounts
In another sheet,
Assume you have set up the cross-tab table, unique Names listed in A2 down
(eg: Matt), unique Accounts in B1 across (eg: Medium) -- note that the pivot
approach does this bit for you automatically
Put in B2:
=SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$B$2:$B$100=B$1))
Copy B2 across/fill down to populate the table. Any worth? hit YES below to
wave