Lookup one value and count in two ranges

[Leanne at Work]

Good morning,

This one has been giving me nightmares!
I'm using Excel 2003 and am trying to do the following -

I have a spread sheet which lists all customers, their representatives names
and what products they buy. EG:
A B C D E
1. REP CUST PCT 1 PCT 2 PCT 3
2. John Cust 1 2 1 ""
3. John Cust 2 "" 2 5
4. Jack Cust 3 3 "" 1
5. John Cust4 "" 1 ""

(""= blank cells)

And on another sheet I have a table which is populated by using 3 drop down
boxes. For example - a user can select a representatives name and two
products and see some summary details populate in the cells below.
I've been trying to find a way to add to this, a formula which will look up
all the entries for one representative, and then count how many customers
they have for the two products selected. Ie - If I searched for John and
wanted to see results for products 1 & 2, the correct result would be 3. (3
customers)

So far I've only been able to find a way to count each time a product is
recorded - so when searching for John and Products 1 and 2 - I currently get
a 4.

I would appreciate any and all help on this. Thank you for your time.

Kind Regards
Leanne

 

[Lars-Åke Aspelin]

On Tue, 29 Jul 2008 15:28:04 -0700, Leanne at Work <Leanne at

Good morning,

This one has been giving me nightmares!
I'm using Excel 2003 and am trying to do the following -

I have a spread sheet which lists all customers, their representatives names
and what products they buy. EG:
A B C D E
1. REP CUST PCT 1 PCT 2 PCT 3
2. John Cust 1 2 1 ""
3. John Cust 2 "" 2 5
4. Jack Cust 3 3 "" 1
5. John Cust4 "" 1 ""

(""= blank cells)

And on another sheet I have a table which is populated by using 3 drop down
boxes. For example - a user can select a representatives name and two
products and see some summary details populate in the cells below.
I've been trying to find a way to add to this, a formula which will look up
all the entries for one representative, and then count how many customers
they have for the two products selected. Ie - If I searched for John and
wanted to see results for products 1 & 2, the correct result would be 3. (3
customers)

So far I've only been able to find a way to count each time a product is
recorded - so when searching for John and Products 1 and 2 - I currently get
a 4.

I would appreciate any and all help on this. Thank you for your time.

Kind Regards

If the rep name is in cell A1, the first selected product name is in
cell B1, and the second selected product name is in cell C1 you may
try the following formula to get the number of customers.
(I assume that there is never more than one row per customer in the
table that is in another sheet, Sheet1, columns A to E, starting on
row 2)

=SUMPRODUCT(- -(Sheet1!A2:A5=A1),- -(-
-(OFFSET(Sheet1!B2:B5,0,MATCH(B1,Sheet1!C1:E1,0))<>0)+(OFFSET(Sheet1!B2:B5,0,MATCH(C1,Sheet1!C1:E1,0))>0)>0))

If you have more more customers, just change the A5 and B5 to be big
enough
If you have more than three products, just change C1:E1 to cover all
the product names.

Hope this helps / Lars-Åke

 

[Suleman Peerzade]

Hi,

What you need to do is have the data first sorted in ascending order. This
is what you will see
REP CUST PCT 1 PCT 2 PCT 3
Jack Cust 3 3 1
John Cust 1 2 1
John Cust 4 1
John Cust 2 2 5
The you need to select the entire table go to Data>Subtotals.

In Subtotal select each change in Rep and use function Count.
Then check the Cust check box.
Do not forget to uncheck the check box that says replace the earlier
subtotals.
say ok
This will give you the desired result. To give this to a user you need to
make the subtotals first and then you can give them a copy of your work sheet.

 

[Suleman Peerzade]

The result would be
REP CUST PCT 1 PCT 2 PCT 3
Jack Cust 3 3 1
Jack Count 1
John Cust 1 2 1
John Cust 4 1
John Cust 2 2 5
John Count 3
Grand Count 4

 

[Lars-Åke Aspelin]

The result would be
REP CUST PCT 1 PCT 2 PCT 3
Jack Cust 3 3 1
Jack Count 1
John Cust 1 2 1
John Cust 4 1
John Cust 2 2 5
John Count 3
Grand Count 4

This shows that the REP John has three customers in total.
But the question was to count the customers for a selected rep
"for the two products selected".
If product PCT 1 and PCT 3 are selected (on the other worksheet) the
expected result would be 2, as John has only two customers, Cust 1 and
Cust 2 for the selected products.
Your subtotal solution does not show how to take care of "the two
product selected". Or am I missing something here?

Lars-Åke