+ Reply to Thread
Results 1 to 7 of 7

Sum like items

  1. #1
    JCowell
    Guest

    Sum like items

    I have a report that keeps track of:
    Product #, Customer #, Qty Sold for every invoice on a specific date

    I want to add/SUM all the (Qty Sold) for all Like (Product #'s) regardless
    who they were purchased by. To give me my total units sold.

    And also be able to SUM the (Qty Sold) for each specific Customer. To give
    me that Customers Total Useage.



  2. #2
    Roger Govier
    Guest

    Re: Sum like items

    Hi

    One way, put Product item in F2 and Customer name in G2
    =SUMPRODUCT(--($A$2:$A$1000=F2),$C$2:$C$1000) for total Product units
    sold
    =SUMPRODUCT(--($B$2:$B$1000=G2),$C$2:$C$1000) for Total sold to a given
    Customer
    =SUMPRODUCT(--($A$2:$A$1000=F2),--($B$2:$B$1000=G2),$C$2:$C$1000) for
    Given Customer and Given product


    Better still, create a Pivot Table and see an analysis of all Customers
    by all Products.
    For help on setting up a Pivot Table take a look at Debra Dalgleish's
    site and scroll down to Pivot Tables
    http://www.contextures.com/tiptech.html
    --
    Regards

    Roger Govier


    "JCowell" <[email protected]> wrote in message
    news:[email protected]...
    >I have a report that keeps track of:
    > Product #, Customer #, Qty Sold for every invoice on a specific date
    >
    > I want to add/SUM all the (Qty Sold) for all Like (Product #'s)
    > regardless
    > who they were purchased by. To give me my total units sold.
    >
    > And also be able to SUM the (Qty Sold) for each specific Customer. To
    > give
    > me that Customers Total Useage.
    >
    >




  3. #3
    JCowell
    Guest

    Re: Sum like items

    I am trying to do this for over 200,000 unique items and hundreds of customers.

    I can't put item in F2 for thousands of items. Can I?

    "Roger Govier" wrote:

    > Hi
    >
    > One way, put Product item in F2 and Customer name in G2
    > =SUMPRODUCT(--($A$2:$A$1000=F2),$C$2:$C$1000) for total Product units
    > sold
    > =SUMPRODUCT(--($B$2:$B$1000=G2),$C$2:$C$1000) for Total sold to a given
    > Customer
    > =SUMPRODUCT(--($A$2:$A$1000=F2),--($B$2:$B$1000=G2),$C$2:$C$1000) for
    > Given Customer and Given product
    >
    >
    > Better still, create a Pivot Table and see an analysis of all Customers
    > by all Products.
    > For help on setting up a Pivot Table take a look at Debra Dalgleish's
    > site and scroll down to Pivot Tables
    > http://www.contextures.com/tiptech.html
    > --
    > Regards
    >
    > Roger Govier
    >
    >
    > "JCowell" <[email protected]> wrote in message
    > news:[email protected]...
    > >I have a report that keeps track of:
    > > Product #, Customer #, Qty Sold for every invoice on a specific date
    > >
    > > I want to add/SUM all the (Qty Sold) for all Like (Product #'s)
    > > regardless
    > > who they were purchased by. To give me my total units sold.
    > >
    > > And also be able to SUM the (Qty Sold) for each specific Customer. To
    > > give
    > > me that Customers Total Useage.
    > >
    > >

    >
    >
    >


  4. #4
    Roger Govier
    Guest

    Re: Sum like items

    I did say a Pivot Table would be a far better idea.

    However, I you truly do have over 200,000 Unique Items, then Excel is
    not the answer for you (currently), as there is a limit of 65536 rows
    per sheet. Assuming that more than one customer bought at least one of
    these items, then the size of the database will be huge and needs to be
    in a database application, not in a spreadsheet.

    --
    Regards

    Roger Govier


    "JCowell" <[email protected]> wrote in message
    news:[email protected]...
    >I am trying to do this for over 200,000 unique items and hundreds of
    >customers.
    >
    > I can't put item in F2 for thousands of items. Can I?
    >
    > "Roger Govier" wrote:
    >
    >> Hi
    >>
    >> One way, put Product item in F2 and Customer name in G2
    >> =SUMPRODUCT(--($A$2:$A$1000=F2),$C$2:$C$1000) for total Product units
    >> sold
    >> =SUMPRODUCT(--($B$2:$B$1000=G2),$C$2:$C$1000) for Total sold to a
    >> given
    >> Customer
    >> =SUMPRODUCT(--($A$2:$A$1000=F2),--($B$2:$B$1000=G2),$C$2:$C$1000) for
    >> Given Customer and Given product
    >>
    >>
    >> Better still, create a Pivot Table and see an analysis of all
    >> Customers
    >> by all Products.
    >> For help on setting up a Pivot Table take a look at Debra Dalgleish's
    >> site and scroll down to Pivot Tables
    >> http://www.contextures.com/tiptech.html
    >> --
    >> Regards
    >>
    >> Roger Govier
    >>
    >>
    >> "JCowell" <[email protected]> wrote in message
    >> news:[email protected]...
    >> >I have a report that keeps track of:
    >> > Product #, Customer #, Qty Sold for every invoice on a specific
    >> > date
    >> >
    >> > I want to add/SUM all the (Qty Sold) for all Like (Product #'s)
    >> > regardless
    >> > who they were purchased by. To give me my total units sold.
    >> >
    >> > And also be able to SUM the (Qty Sold) for each specific Customer.
    >> > To
    >> > give
    >> > me that Customers Total Useage.
    >> >
    >> >

    >>
    >>
    >>




  5. #5
    JCowell
    Guest

    Re: Sum like items

    I do appreciate your help. I will look into and try to figure out pivot
    tables.

    I'm a salesman for a fishing tackle company and we have always told our
    customer that we have over 200,000 independent SKU's between three
    warehouses. However, odly enough when I look at the total number of lines on
    our catalog that I just recently downloaded I only show 61,483.

    I'm an honest salesman if there ever was one and I'll definately quiet
    promoting our over 200,000 SKU's.


    Thanks again-

    Justin Cowell

    "Roger Govier" wrote:

    > I did say a Pivot Table would be a far better idea.
    >
    > However, I you truly do have over 200,000 Unique Items, then Excel is
    > not the answer for you (currently), as there is a limit of 65536 rows
    > per sheet. Assuming that more than one customer bought at least one of
    > these items, then the size of the database will be huge and needs to be
    > in a database application, not in a spreadsheet.
    >
    > --
    > Regards
    >
    > Roger Govier
    >
    >
    > "JCowell" <[email protected]> wrote in message
    > news:[email protected]...
    > >I am trying to do this for over 200,000 unique items and hundreds of
    > >customers.
    > >
    > > I can't put item in F2 for thousands of items. Can I?
    > >
    > > "Roger Govier" wrote:
    > >
    > >> Hi
    > >>
    > >> One way, put Product item in F2 and Customer name in G2
    > >> =SUMPRODUCT(--($A$2:$A$1000=F2),$C$2:$C$1000) for total Product units
    > >> sold
    > >> =SUMPRODUCT(--($B$2:$B$1000=G2),$C$2:$C$1000) for Total sold to a
    > >> given
    > >> Customer
    > >> =SUMPRODUCT(--($A$2:$A$1000=F2),--($B$2:$B$1000=G2),$C$2:$C$1000) for
    > >> Given Customer and Given product
    > >>
    > >>
    > >> Better still, create a Pivot Table and see an analysis of all
    > >> Customers
    > >> by all Products.
    > >> For help on setting up a Pivot Table take a look at Debra Dalgleish's
    > >> site and scroll down to Pivot Tables
    > >> http://www.contextures.com/tiptech.html
    > >> --
    > >> Regards
    > >>
    > >> Roger Govier
    > >>
    > >>
    > >> "JCowell" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> >I have a report that keeps track of:
    > >> > Product #, Customer #, Qty Sold for every invoice on a specific
    > >> > date
    > >> >
    > >> > I want to add/SUM all the (Qty Sold) for all Like (Product #'s)
    > >> > regardless
    > >> > who they were purchased by. To give me my total units sold.
    > >> >
    > >> > And also be able to SUM the (Qty Sold) for each specific Customer.
    > >> > To
    > >> > give
    > >> > me that Customers Total Useage.
    > >> >
    > >> >
    > >>
    > >>
    > >>

    >
    >
    >


  6. #6
    Roger Govier
    Guest

    Re: Sum like items

    Hi Justin

    Well aren't all salesmen honest!! <bg>

    I guess that in any time span you are looking at, not every one of the
    SKU's will have moved and the problem will be smaller than you first
    imagine.
    Do take the time to learn and play with Pivot Tables.
    They are fascinating, and your efforts will be well rewarded.
    I do believe there's an excellent book on its way from Debra in the next
    month - unless the publishers miss their deadlines of course!!

    --
    Regards

    Roger Govier


    "JCowell" <[email protected]> wrote in message
    news:[email protected]...
    >I do appreciate your help. I will look into and try to figure out
    >pivot
    > tables.
    >
    > I'm a salesman for a fishing tackle company and we have always told
    > our
    > customer that we have over 200,000 independent SKU's between three
    > warehouses. However, odly enough when I look at the total number of
    > lines on
    > our catalog that I just recently downloaded I only show 61,483.
    >
    > I'm an honest salesman if there ever was one and I'll definately quiet
    > promoting our over 200,000 SKU's.
    >
    >
    > Thanks again-
    >
    > Justin Cowell
    >
    > "Roger Govier" wrote:
    >
    >> I did say a Pivot Table would be a far better idea.
    >>
    >> However, I you truly do have over 200,000 Unique Items, then Excel is
    >> not the answer for you (currently), as there is a limit of 65536 rows
    >> per sheet. Assuming that more than one customer bought at least one
    >> of
    >> these items, then the size of the database will be huge and needs to
    >> be
    >> in a database application, not in a spreadsheet.
    >>
    >> --
    >> Regards
    >>
    >> Roger Govier
    >>
    >>
    >> "JCowell" <[email protected]> wrote in message
    >> news:[email protected]...
    >> >I am trying to do this for over 200,000 unique items and hundreds of
    >> >customers.
    >> >
    >> > I can't put item in F2 for thousands of items. Can I?
    >> >
    >> > "Roger Govier" wrote:
    >> >
    >> >> Hi
    >> >>
    >> >> One way, put Product item in F2 and Customer name in G2
    >> >> =SUMPRODUCT(--($A$2:$A$1000=F2),$C$2:$C$1000) for total Product
    >> >> units
    >> >> sold
    >> >> =SUMPRODUCT(--($B$2:$B$1000=G2),$C$2:$C$1000) for Total sold to a
    >> >> given
    >> >> Customer
    >> >> =SUMPRODUCT(--($A$2:$A$1000=F2),--($B$2:$B$1000=G2),$C$2:$C$1000)
    >> >> for
    >> >> Given Customer and Given product
    >> >>
    >> >>
    >> >> Better still, create a Pivot Table and see an analysis of all
    >> >> Customers
    >> >> by all Products.
    >> >> For help on setting up a Pivot Table take a look at Debra
    >> >> Dalgleish's
    >> >> site and scroll down to Pivot Tables
    >> >> http://www.contextures.com/tiptech.html
    >> >> --
    >> >> Regards
    >> >>
    >> >> Roger Govier
    >> >>
    >> >>
    >> >> "JCowell" <[email protected]> wrote in message
    >> >> news:[email protected]...
    >> >> >I have a report that keeps track of:
    >> >> > Product #, Customer #, Qty Sold for every invoice on a specific
    >> >> > date
    >> >> >
    >> >> > I want to add/SUM all the (Qty Sold) for all Like (Product #'s)
    >> >> > regardless
    >> >> > who they were purchased by. To give me my total units sold.
    >> >> >
    >> >> > And also be able to SUM the (Qty Sold) for each specific
    >> >> > Customer.
    >> >> > To
    >> >> > give
    >> >> > me that Customers Total Useage.
    >> >> >
    >> >> >
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>




  7. #7
    Registered User
    Join Date
    01-30-2006
    Posts
    30

    Cool Why Macro

    Why dont u use simple Pivot Table Wizard ? Why to go for macro When simple solution is available!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1