# SUMPRODUCT

#### SiskHall

##### New Member
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???

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.

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
ABCDEFGHIJ
1NameDateName
2Suzie1/1/2005month/yearSuzieTomBobMary
3Tom2/5/2005Jan-052121
4Bob1/31/2005Feb-050112
5Mary1/10/2005Mar-050000
6Suzie1/14/2005Apr-050000
7Bob2/15/2005May-050000
8Mary2/9/2005Jun-050000
9Tom1/6/2005Jul-050000
10Mary2/25/2005Aug-050000
11Bob1/3/2005Sep-050000
12Oct-050000
13Nov-050000
14Dec-050000
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))

Replies
5
Views
233
Replies
2
Views
215
Replies
12
Views
560
Replies
8
Views
368
Replies
2
Views
364

1,203,140
Messages
6,053,729
Members
444,681
Latest member

### 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.

### Which adblocker are you using?

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

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