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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
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.
 
Upvote 0
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:
 
Upvote 0
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?
 
Upvote 0
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...
 
Upvote 0
.

I have to make look exactly like this.

helpme1.jpg


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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top