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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|