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
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.
One test is worth a thousand opinions.
Click the * below to say thanks.
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
Hi,
See two possible Pivot Tables with your data. Pivots aren't that hard and you don't need formulas.
See what you think.
One test is worth a thousand opinions.
Please see next message, thanks
Last edited by jayjaysb; 11-16-2011 at 05:00 PM.
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
Hi,
Put this in B2, Pull it down and across.
=COUNTIFS(Sheet1!$B$1:$B$8,Sheet2!$A2,Sheet1!$A$1:$A$8,Sheet2!B$1)
One test is worth a thousand opinions.
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 08:12 PM.
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.
One test is worth a thousand opinions.
Thanks Marvin, I have simply inserted lines above 8, and this has worked, thanks again for your patience. Cheers. JJSB
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
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.
One test is worth a thousand opinions.
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
Reply With Quote
Bookmarks