# Sum with Multiple Criteria

#### lionstud31

##### Board Regular
I am trying to find a way to get a sum only if it meets more than one requirement. Here's what is is: in column A, I have the employee's names. In column B, I have the date. In column C, I have a dollar amount. What I want to do is get a total for each person's transactions in a certain date range. For example, I want the total John sold in September. Can anyone of you excel Wiz's out there help me?

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Domenic

##### MrExcel MVP
Assuming that Column B contains true date values, try...

=SUMPRODUCT(--(\$A\$2:\$A\$100=E2),--(\$B\$2:\$B\$100-DAY(\$B\$2:\$B\$100)+1=F2),\$C\$2:\$C\$100)

...where E2 contains the name, such as John, and F2 contains the first day of the month and year of interest, such as September 1, 2006.

Hope this helps!

#### Brian from Maui

##### MrExcel MVP
You can also use a Pivot Table and group by months.

#### lionstud31

##### Board Regular
someone told me to use:

=sumproduct((A2:a100="John")*(month(b2:b100)=9)*(c2:c100))

but it didnt work. Any other suggestions??

#### lionstud31

##### Board Regular

someone told me to use:

=sumproduct((A2:a100="John")*(month(b2:b100)=9)*(c2:c100))

but it didnt work. Any other suggestions??

##### Well-known Member
i'm no wiz lion, but i think your formula will not sum the transactions it will count them as you have one too many brackets, Domenic's looks good to me

#### lionstud31

##### Board Regular

how do i get f2 to contain the first day of the month and year of interest

#### Domenic

##### MrExcel MVP
how do i get f2 to contain the first day of the month and year of interest

As an example, for the month of October and the year 2006, enter the date as...

October 1, 2006

or

October 2006

#### Brian from Maui

##### MrExcel MVP
someone told me to use:

=sumproduct((A2:a100="John")*(month(b2:b100)=9)*(c2:c100))

but it didnt work. Any other suggestions??

What doesn't work?

Replies
1
Views
445
Replies
1
Views
172
Replies
3
Views
207
Replies
3
Views
126
Replies
12
Views
274

1,141,630
Messages
5,707,520
Members
421,512
Latest member
jc364698

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

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