Prev: Linking Equations to Highlights
Next: View Message when Mouse Hovers over Cell. (Not the Comments Feild)
From: KDenise on 30 May 2010 21:00
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
From: T. Valko on 30 May 2010 21:17
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" <KDenise(a)discussions.microsoft.com> wrote in message
news:C621BA8C-33E3-4A04-9BF9-2A25AFF7A67F(a)microsoft.com...
> 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


From: Ashish Mathur on 30 May 2010 21:26
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" <KDenise(a)discussions.microsoft.com> wrote in message
news:C621BA8C-33E3-4A04-9BF9-2A25AFF7A67F(a)microsoft.com...
> 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

From: Max on 30 May 2010 21:31
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
From: Jacob Skaria on 30 May 2010 21:35
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))


--
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
 |  Next  |  Last
Pages: 1 2
Prev: Linking Equations to Highlights
Next: View Message when Mouse Hovers over Cell. (Not the Comments Feild)