Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    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

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    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.


    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Count Products (Excel 2002/2003)

    HI Hans

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

    Thanks again

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •