Need Help with Sumif Function including dates
 

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

Need Help with Sumif Function including dates
 

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)


--
Biff
Microsoft Excel MVP


"KDenise" wrote in message
...
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

Need Help with Sumif Function including dates
 

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

"KDenise" wrote in message
...
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

Need Help with Sumif Function including dates
 

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)
--
Max
Singapore
---
"KDenise" wrote:
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

Need Help with Sumif Function including dates
 

Try

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

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


--
Jacob (MVP - Excel)


"KDenise" wrote:

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

Need Help with Sumif Function including dates
 

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
Singapore
---
"KDenise" wrote:
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

Need Help with Sumif Function including dates
 

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