Need Help with Sumif Function including dates


K

KDenise

Hi,

I need some assistance with tracking my commissions that are due to be paid
each month. The "Comm Due Date" is in column "C", rows 3 - 30 which list by
date all of the Commissions Due to be paid and the "Comm Balance" is in
column "H", rows 3 - 30 which has the Commission Balance due for each sale.
I tried utilizing a formula from a post that I found from April '05 but for
some reason I keep receiving a #NAME? error. I'm not sure what I am doing
wrong. I want to find the total due by month so that as I add additional
sales to the spreadsheet it will update the total due for each month. Can
anyone please help me?

=SUMIF(Comm_Due_Date,">=04/01/2010",Comm_Balance)+SUMIF(Comm_Due_Date,"<=04/30/2010",Comm_Balance)

If you need additional information, please let me know.
KDenise
 
Ad

Advertisements

T

T. Valko

Try it like this using cells to hold the date boundaries...

A1 = lower date boundary = 4/1/2010
B1 = upper date boundary = 4/30/2010

=SUMIF(Comm_Due_Date,">="&A1,Comm_Balance)-SUMIF(Comm_Due_Date,">"&B1,Comm_Balance)
 
A

Ashish Mathur

Hi,

Strange that you see the name error because that usually appears when a
function is spelt incorrectly - which does not seem to be the case. If you
wish to sum the amount that falls between two dates, you may use the
following

=SUMIF(Comm_Due_Date,">=04/01/2010",Comm_Balance)-SUMIF(Comm_Due_Date,">04/30/2010",Comm_Balance)

or

=sumproduct((Comm_Due_Date>=04/01/2010)*(Comm_Due_Date<=04/30/2010)*(Comm_Balance))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
M

Max

You can use this to compute the sum of col B for dates in col A falling in
Apr 2010
=SUMPRODUCT(--(TEXT(A2:A100,"mmmyyyy")="Apr2010"),B2:B100)
Adapt to suit. I prefer to use the above as I don't have to recall what date
is the last day of the particular month/yr (30th?, 31st? ugh)
 
J

Jacob Skaria

Try

=SUMPRODUCT((TEXT(C3:C30,"mmmyyyy")="Apr2010")*(H3:H30))

or with query date in cell E1
=SUMPRODUCT((TEXT(C3:C30,"mmmyyyy")=TEXT(E1,"mmmyyyy"))*(H3:H30))
 
M

Max

well, if you want to use SUMIF to get the sum of col B for dates in col A in
Apr 2010, this seems to work fine:
=SUMIF(A:A,">="&--"1 Apr 2010",B:B)-SUMIF(A:A,">="&--"1 May 2010",B:B)
Care should be taken to be unambiguous when dealing with dates
 
Ad

Advertisements

M

Max

Note the careful avoidance of having to specify the last day of the month
(the "ugh" moment) in the earlier SUMIF expression <g>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top