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

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
Joined
Mar 10, 2004
Messages
19,726
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!
 

lionstud31

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

ADVERTISEMENT

someone told me to use:

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

but it didnt work. Any other suggestions??
 

Paddy1979

Well-known Member
Joined
Sep 23, 2005
Messages
608
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
Joined
Oct 16, 2006
Messages
89

ADVERTISEMENT

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

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726
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
 

Forum statistics

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

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
Top