Count Monthly Data for a user (SUMPRODUCT?)

[phive_]

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.

 

[Von Pookie]

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
	A	B	C	D	E
1	AnalystName	Fortheweekof	CountOfID	Month
2	BobSmith	5/13/2006	24	5
3	BobSmith	5/20/2006	29	5
4	BobSmith	7/6/2006	15	7
5	BobSmith	7/6/2006	45	7
6	JohnDoe	5/13/2006	27	5
7	JohnDoe	5/20/2006	19	5
8	JohnDoe	6/20/2006	17	6
9	JohnDoe	6/6/2006	11	6
10
11
12	CountofMonth	Month
13	AnalystName	5	6	7	GrandTotal
14	BobSmith	2		2	4
15	JohnDoe	2	2		4
16	GrandTotal	4	2	2	8
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]

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_]

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!

 

[Norie]

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]

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
	L	M	N	O	P	Q
10				BobSmith	May	2
11				BobSmith	5	2
12
13	BobSmith	5/13/2006	24
14	BobSmith	5/20/2006	29
15	BobSmith	7/6/2006	15
16	BobSmith	7/6/2006	45
17	JohnDoe	5/13/2006	27
18	JohnDoe	5/20/2006	19
19	JohnDoe	6/20/2006	17
20	JohnDoe	6/6/2006	11
Sheet1

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

 

[phive_]

.

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]

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]

Book1
	A	B	C	D	E	F	G	H
1	AnalystName	Fortheweekof	CountOfID		AnalystName	1-May-06	1-Jun-06	1-Jul-06
2	Bob Smith	5/13/2006	24		Bob Smith	2	0	2
3	Bob Smith	5/20/2006	29		John Doe	2	2	0
4	Bob Smith	7/6/2006	15
5	Bob Smith	7/6/2006	45
6	John Doe	5/13/2006	27
7	John Doe	5/20/2006	19
8	John Doe	6/20/2006	17
9	John Doe	6/6/2006	11
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_]

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