Count Monthly Data for a user (SUMPRODUCT?)

phive_

New Member
Joined
Mar 3, 2005
Messages
38
Hello all,
"Todays" Problem is that I’m trying to count total data (total count) that occurs in a month for a particular user (dynamic) in a sheet.

Data:
I have three columns on this particular sheet (we'll call it "RawData").
COL A: FortheWeekOf – Date of the last day of that week
COL B: AnaystsName – Analysts that handled the particular problem
COL C: CountOfID – Number Times that week that analyst handled a problem (total)

Example:
Fortheweekof | AnalystName | CountOfID |
Bob Smith | 05/13/2006 | 24 |
Bob Smith | 05/20/2006 | 29
Bob Smith | 07/06/2006 | 15 |
Bob Smith | 07/06/2006 | 45 |
John Doe | 05/13/2006 | 27 |
John Doe | 05/20/2006 | 19 |
John Doe | 06/20/2006 | 17 |
John Doe | 06/06/2006 | 11 |

So in the above data, on another sheet I would have:
Bob Smith: May = 2, June = 0, July = 2
John Doe: May = 2, June = 2, July = 0
(all in seperate cells of course)

I think a SUMPRODUCT would be used to do this, but for the life of me I can't figure it out.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
If you add another column to pull out the month from the dates, you could just use a pivot table.

Say your data is in columns A:C. In D1, add a header of "Month" and in D2 add the following formula =MONTH(B2) and copy it down to the rest of the rows. Then make a pivot table using the data to automatically get your results.

For example:
Book2
ABCDE
1AnalystNameFortheweekofCountOfIDMonth
2BobSmith5/13/2006245
3BobSmith5/20/2006295
4BobSmith7/6/2006157
5BobSmith7/6/2006457
6JohnDoe5/13/2006275
7JohnDoe5/20/2006195
8JohnDoe6/20/2006176
9JohnDoe6/6/2006116
10
11
12CountofMonthMonth
13AnalystName567GrandTotal
14BobSmith224
15JohnDoe224
16GrandTotal4228
Sheet1


A1:D9 is the data, column D contains a formula to return the month number from the date in column B.

A12:E16 is the pivot table created from that data.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
phive

As Kristy has suggested you could use a pivot table.

But there's no need for the extra column, just add the date as a column item and then group it by months.

Note this will only work if you have true dates and no blanks in the date column.
 

phive_

New Member
Joined
Mar 3, 2005
Messages
38
Thanks for the reply

Thats what I'm using now, but I'd like to make something more dynamic / clean (something a pivot table can't offer).

If all else fails, I will gladly use your idea.

Thank you for taking the time to reply! :biggrin:
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Re: Thanks for the reply

Thats what I'm using now, but I'd like to make something more dynamic / clean (something a pivot table can't offer).
What do you mean?

Why wouldn't a pivot table offer that?
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
The formula is simpler if you can either specify (or calculate) the numerical value for the month in another cell... I built the formula both ways, so you can decide. Note that I also assume dthat it would for dates THIS year... if you want to get info on other years, make th eneeded change.
Book1
LMNOPQ
10BobSmithMay2
11BobSmith52
12
13BobSmith5/13/200624
14BobSmith5/20/200629
15BobSmith7/6/200615
16BobSmith7/6/200645
17JohnDoe5/13/200627
18JohnDoe5/20/200619
19JohnDoe6/20/200617
20JohnDoe6/6/200611
Sheet1


EDIT: Woops, I summed your ID's by mistake... here is the correct formulas...
 

phive_

New Member
Joined
Mar 3, 2005
Messages
38

ADVERTISEMENT

.

I have to make look exactly like this.



BMC would be the total for the month on "RawData1"
RED would be the total for the month on "RawData2"
and
TOT would be the total of both of the latter.
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Your image does not work. You need to upload it to the internet and reference it from there. Looks like you may be trying to show an image located on an intranet (I could be wrong, though), which won't work.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Book1
ABCDEFGH
1AnalystNameFortheweekofCountOfIDAnalystName1-May-061-Jun-061-Jul-06
2Bob Smith5/13/200624Bob Smith202
3Bob Smith5/20/200629John Doe220
4Bob Smith7/6/200615
5Bob Smith7/6/200645
6John Doe5/13/200627
7John Doe5/20/200619
8John Doe6/20/200617
9John Doe6/6/200611
10
RawData


F2:

=SUM(IF($A$2:$A$9=$E2,IF($B$2:$B$9-DAY($B$2:$B$9)+1=F$1,IF($C$2:$C$9,1))))

which is confirmed with control+shift+enter (not with enter) then copied across and down.
 

phive_

New Member
Joined
Mar 3, 2005
Messages
38
PERFECT

That works perfectly Aladin, thank you!!!


Now....
For another sheet, i have to add up the numebrs of "CountOf" for that month.

So again (with sample data):
Fortheweekof | AnalystName | CountOfID |
Bob Smith | 05/13/2006 | 24 |
Bob Smith | 05/20/2006 | 29
Bob Smith | 07/06/2006 | 15 |
Bob Smith | 07/06/2006 | 45 |
John Doe | 05/13/2006 | 27 |
John Doe | 05/20/2006 | 19 |
John Doe | 06/20/2006 | 17 |
John Doe | 06/06/2006 | 11 |

Bob Smith: May = 53, June = 0, July = 60
John Doe: May = 46, June = 28, July = 0

Again, any help with this stuff would be great :confused:
 

Watch MrExcel Video

Forum statistics

Threads
1,114,041
Messages
5,545,687
Members
410,698
Latest member
Wloven
Top