Need Help with Sumif Function including dates

[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

 

[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)

 

[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

 

[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)

 

[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))

 

[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

 

[Max]

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