A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Need Help with Sumif Function including dates



 
 
Thread Tools Display Modes
  #1  
Old May 31st, 2010, 02:00 AM posted to microsoft.public.excel.worksheet.functions
KDenise
external usenet poster
 
Posts: 1
Default 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
  #2  
Old May 31st, 2010, 02:17 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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



  #3  
Old May 31st, 2010, 02:26 AM posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
 
Posts: 1,764
Default 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


  #4  
Old May 31st, 2010, 02:31 AM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 8,574
Default 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

  #5  
Old May 31st, 2010, 02:35 AM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default 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

  #6  
Old May 31st, 2010, 02:52 AM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 8,574
Default 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

  #7  
Old May 31st, 2010, 04:47 AM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 8,574
Default 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
---
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:33 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.