# Cumulative total on months by Agent

1. ## 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. ## 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.

3. ## 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

4. ## 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.

6. ## 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

7. ## 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.``

8. ## 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.

9. ## 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. ## 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. ## 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. ## 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. ## 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

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

#### 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