+ Reply to Thread
Results 1 to 13 of 13

Cumulative total on months by Agent

  1. #1
    Registered User
    Join Date
    12-15-2009
    Location
    Glasgow
    MS-Off Ver
    Excel 2003
    Posts
    22

    Cumulative total on months by Agent

    Im looking for some help please,

    I am looking to input a cumulative total for each month by agent see below, so it shows Jim had 1 in Jan & 1 in Feb, Mary had 1 in Feb, Harry had 1 in Mar and Sally had 2 in Mar, Hope this makes sense, Any help would be appreciated, Thanks jayjay

    Month Agent Customer
    Jan Jim John Smith
    Feb Mary Karen Jones
    Feb Jim Peter Smyth
    Mar Harry Amy Carr
    Mar Sally Jamie Black
    Mar Sally Katy Cochrane

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Cumulative total on months by Agent

    Hi jayjaysb ,

    I've created some sample data and done a Pivot Table on it to try to answer your question. I hope 2003 excel can display the Date Groups and show customers/agent.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    12-15-2009
    Location
    Glasgow
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Cumulative total on months by Agent

    Sweet Jesus Marvin, way too clever for me, but totally brilliant I have attached my makeshift file for you to look at, its way more basic than yours. Pls have a look, Cheers JJSB
    Attached Files Attached Files

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Cumulative total on months by Agent

    Hi,

    See two possible Pivot Tables with your data. Pivots aren't that hard and you don't need formulas.

    See what you think.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-15-2009
    Location
    Glasgow
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Cumulative total on months by Agent

    Please see next message, thanks
    Last edited by jayjaysb; 11-16-2011 at 06:00 PM.

  6. #6
    Registered User
    Join Date
    12-15-2009
    Location
    Glasgow
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Cumulative total on months by Agent

    Marvin, thanks for your help, but I was looking for a formula so it auto updates. Pls check sheet 2 (note I have manually typed the numbers) Thanks again JJSB
    Attached Files Attached Files

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Cumulative total on months by Agent

    Hi,

    Put this in B2, Pull it down and across.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-15-2009
    Location
    Glasgow
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Cumulative total on months by Agent

    Aww Marvin, You De Man, much obliged to you. Thanks for ALL your help. Excellent!!!!Oh one last thing Marvin, if I now add to the rows in sheet A, how do i get it to autofill in sheet 2, as the query is only going to $B$8, if you get me.
    Last edited by jayjaysb; 11-16-2011 at 09:12 PM.

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Cumulative total on months by Agent

    Hi,

    You will need to modify the formula to span all the data on sheet1. OR
    You might use a named range for the data on sheet1. Or
    You might use a Dynamic Named Range for the data on Sheet1.

  10. #10
    Registered User
    Join Date
    12-15-2009
    Location
    Glasgow
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Cumulative total on months by Agent

    Thanks Marvin, I have simply inserted lines above 8, and this has worked, thanks again for your patience. Cheers. JJSB

  11. #11
    Registered User
    Join Date
    12-15-2009
    Location
    Glasgow
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Cumulative total on months by Agent

    Marvin, I completed my spreadsheet and emailed it to work, it worked fine but today the formula came up with an error like #NAME#. Im not sure what excel version it is but =COUNTIFS(Sheet1!$B$1:$B$8,Sheet2!$A2,Sheet1!$A$1:$A$8,Sheet2!B$1)
    no longer works, do you know what else I could use to remedy it. Would sumproduct work if so how would i do it. Thanks again JJSB

  12. #12
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Cumulative total on months by Agent

    Hi JJSB

    CountifS (with an S on the end) works in 2007 and 2010. I don't think they had this in 2003 version of Excel.

  13. #13
    Registered User
    Join Date
    12-15-2009
    Location
    Glasgow
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Cumulative total on months by Agent

    Hi Marvin, I think I've got it to work, I have used
    =SUMPRODUCT(--(Sheet1!$B$1:$B$8=Sheet2!$A2),--(Sheet1!$A$1:$A$8=Sheet2!B$1))
    and this seems to work. Much obliged to you. JJSB

+ 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