+ Reply to Thread
Results 1 to 3 of 3

excel charting formulas - sum of 2 columns with differing data

  1. #1
    John Watson @ barkoff container
    Guest

    excel charting formulas - sum of 2 columns with differing data

    I need help with creating a formula.

    i have 2 columns in my data i need to sum in my chart, 1 column is the sales
    reps name the other column is staus. when a project i work on becomes an
    order i type the word "order" in the status column, but i cant get the 2
    columns to add up in my chart.
    what im trying to do is every time a given sales reps name appears in one
    column and the word "order" appears in the other column in the same row.
    i need to have a sum of how many times this appears, this gives me the
    amount of orders any one sales rep has generated.

    any help would be greatly appreciated, i would be glad to forward my spread
    sheet to any one that would like to look it over and help me out.

    John Watson

  2. #2
    bj
    Guest

    RE: excel charting formulas - sum of 2 columns with differing data

    try
    =sumproduct(--(Sales rep range = "Sales rep"),--(Status range = "Order")
    the --) changes the logic true false to a numeric 1 0
    the arrays in each section must be the same size, but the shorthand full
    column (A:A) won't work.

    "John Watson @ barkoff container" wrote:

    > I need help with creating a formula.
    >
    > i have 2 columns in my data i need to sum in my chart, 1 column is the sales
    > reps name the other column is staus. when a project i work on becomes an
    > order i type the word "order" in the status column, but i cant get the 2
    > columns to add up in my chart.
    > what im trying to do is every time a given sales reps name appears in one
    > column and the word "order" appears in the other column in the same row.
    > i need to have a sum of how many times this appears, this gives me the
    > amount of orders any one sales rep has generated.
    >
    > any help would be greatly appreciated, i would be glad to forward my spread
    > sheet to any one that would like to look it over and help me out.
    >
    > John Watson


  3. #3
    Jon Peltier
    Guest

    Re: excel charting formulas - sum of 2 columns with differing data

    Assuming your data looks like this:

    Sales Rep Status
    Fred Order
    Bill Order
    Fred No Order
    Fred Order
    Bill Order
    Bill No Order
    Fred No Order
    Bill Order

    Select the data and make a Pivot Table (Data menu > Pivot Table Report).
    Drag teh Sales Rep field to the Row area and the Status field to the
    Column area (or switch the two to transpose the table). Now drag the
    Status field (another copy of it) to the Data area. You should get a
    pivot table like this:

    Count of Status Status
    Sales Rep Order No Order Grand Total
    Bill 3 1 4
    Fred 2 2 4
    Grand Total 5 3 8

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______


    John Watson @ barkoff container wrote:

    > I need help with creating a formula.
    >
    > i have 2 columns in my data i need to sum in my chart, 1 column is the sales
    > reps name the other column is staus. when a project i work on becomes an
    > order i type the word "order" in the status column, but i cant get the 2
    > columns to add up in my chart.
    > what im trying to do is every time a given sales reps name appears in one
    > column and the word "order" appears in the other column in the same row.
    > i need to have a sum of how many times this appears, this gives me the
    > amount of orders any one sales rep has generated.
    >
    > any help would be greatly appreciated, i would be glad to forward my spread
    > sheet to any one that would like to look it over and help me out.
    >
    > John Watson


+ 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