Sum with Multiple Criteria

lionstud31

Board Regular
Joined
Oct 16, 2006
Messages
89
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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!
 
Upvote 0
someone told me to use:

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

but it didnt work. Any other suggestions??
 
Upvote 0
someone told me to use:

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

but it didnt work. Any other suggestions??
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

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