# Thread: Count Products (Excel 2002/2003)

1. ## Count Products (Excel 2002/2003)

Hi

In the attached I need to sum how many of each product has been sold to each customer name.

Columns A:E contain all the data.

Columns H:I contain the non duplicated Customers

Columns J:W contain the non duplicated Products

I need to find how many of each product was sold to each Customer Name.

Example;

1287699 Canniesburn Nursing Home 7511830 Good Sense Fresh 6x0.75L MWe 51
1287699 Canniesburn Nursing Home 7512228 Good Sense Vert JFlex 5L S&MWe 269
1287699 Canniesburn Nursing Home 7513506 Good Sense Power 6x1L N&MWe29 19

The number 51 would appear in Cell K15
The number 269 would appear in Cell M15
The number 19 would appear in Cell N15

Can this be done without using a Pivot table

Many Thanks

2. ## Re: Count Products (Excel 2002/2003)

Why don't you want to use a pivot table? It's by far the easiest way to get the table you want. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

If you want to use formulas: enter the following formula in J2:

=SUMPRODUCT((\$A\$2:\$A\$2293=\$H2)*(\$D\$2:\$D\$2293=J\$1)* \$E\$2:\$E\$2293)

and fill down as far as needed, then fill right as far as needed.

Note: you won't get 269 in M15, but in Q15, since "Good Sense Vert JFlex 1.5L S&MWe" is not the same as "Good Sense Vert JFlex 5L S&MWe".

3. ## Re: Count Products (Excel 2002/2003)

Added later: please keep in mind that the formula solution fills 1801 x 14 = 35214 cells with formulas, so recalcution will take some time.

4. ## Re: Count Products (Excel 2002/2003)

Hi Hans

Thank you very much for the formula. I fully understand what you say about the Pivot Table, I already offered this. The manager concerned insists on doing it this way.

5. ## Re: Count Products (Excel 2002/2003)

HI Hans

Thanks for that, this may convince the person involved to accept the pivot table.

Thanks again