SUMPRODUCT

[SiskHall]

I am needing to sum the total amount of cases worked by wach person on a monthly basis.

Example:
Column A Column B

Suzie 1/1/2005
Tom 2/5/2005
Bob 1/31/2005
Mary 1/10/2005
Suzie 1/14/2005
Bob 2/15/2005
Mary 2/9/2005
Tom 1/6/2005
Mary 2/25/2005
Bob 1/3/2005


So the totals would show that Suzie worked 2 cases in Jan, Tom 1 in Jan and 1 in Feb, Bob 2 in Jan and 1 in Feb, Mary 1 in Jan and 2 in Feb...


Is that a countif or a sumproduct that I can use for this???

 

[Oaktree]

Have you considered a pivot table?

Set it up with Name on the left, Count of Name as your data, and the dates at the top.

Then, group your dates by month.

 

[Aladin Akyurek]

A pivot table would be convenient. See:

http://www.peltiertech.com/Excel/Pivots/pivottables.htm

You need to group dates in moths.

A formula approach would be:

Book8
	A	B	C	D	E	F	G	H	I	J
1	Name	Date				Name
2	Suzie	1/1/2005			month/year	Suzie	Tom	Bob	Mary
3	Tom	2/5/2005			Jan-05	2	1	2	1
4	Bob	1/31/2005			Feb-05	0	1	1	2
5	Mary	1/10/2005			Mar-05	0	0	0	0
6	Suzie	1/14/2005			Apr-05	0	0	0	0
7	Bob	2/15/2005			May-05	0	0	0	0
8	Mary	2/9/2005			Jun-05	0	0	0	0
9	Tom	1/6/2005			Jul-05	0	0	0	0
10	Mary	2/25/2005			Aug-05	0	0	0	0
11	Bob	1/3/2005			Sep-05	0	0	0	0
12					Oct-05	0	0	0	0
13					Nov-05	0	0	0	0
14					Dec-05	0	0	0	0
15
Sheet1

Note that E3:E4 is intended to house true dates, each representing the first of the month/year of interest. Given this prerequisite...

F3, copy across then down:

=SUMPRODUCT(--(DATE(YEAR($B$2:$B$11),MONTH($B$2:$B$11),1)=$E3),--($A$2:$A$11=F$2))