|
Date Format Inside of a SUMIF Statement
Greetings,
I am trying to get the sum for each month in a column which has thee entire year. So far I have: SUMIF($B:$B,{need month and year here},$DT:$DT) B is formatted as mm/dd/yy I need mm/yy in the formula. I can't remember how to do this! Anyone have any ideas? Any help would be appreciated. TIA -Minitman |
Minitman wrote:
Greetings, I am trying to get the sum for each month in a column which has thee entire year. So far I have: SUMIF($B:$B,{need month and year here},$DT:$DT) B is formatted as mm/dd/yy I need mm/yy in the formula. I can't remember how to do this! Anyone have any ideas? Any help would be appreciated. TIA -Minitman =SUMIF(B:B,"="&A2,DT:DT)-SUMIF(B:B,""&A3,DT:DT) where A2 is a first day date of the mont/year of interest like 1-Mar-04 and A3 houses: =EOMONTH(A2,0). -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
Give example of your data and expected answer.
Mangesh "Minitman" wrote in message ... Greetings, I am trying to get the sum for each month in a column which has thee entire year. So far I have: SUMIF($B:$B,{need month and year here},$DT:$DT) B is formatted as mm/dd/yy I need mm/yy in the formula. I can't remember how to do this! Anyone have any ideas? Any help would be appreciated. TIA -Minitman |
Hey Mangesh,
B DT 3 1/20/04 $4.00 4 1/21/04 $3.00 5 1/29/04 $2.00 6 2/14/04 $1.00 7 3/12/04 $11.00 8 3/16/04 $6.00 9 2/22/04 $20.00 On a different sheet: Month Amount Jan (Formula goes here, should return $9.00) Feb (Formula goes here, should return $21.00) Mar (Formula goes here, should return $17.00) That is what I am trying to do Any suggestions? -Minitman On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav" wrote: Give example of your data and expected answer. Mangesh "Minitman" wrote in message .. . Greetings, I am trying to get the sum for each month in a column which has thee entire year. So far I have: SUMIF($B:$B,{need month and year here},$DT:$DT) B is formatted as mm/dd/yy I need mm/yy in the formula. I can't remember how to do this! Anyone have any ideas? Any help would be appreciated. TIA -Minitman |
=SUMPRODUCT(--(CHOOSE(MONTH($B$1:$B$7),"Jan","Feb","Mar")=E1),$D T$1:$DT$7)
confirm with control shift enter Mangesh "Minitman" wrote in message ... Hey Mangesh, B DT 3 1/20/04 $4.00 4 1/21/04 $3.00 5 1/29/04 $2.00 6 2/14/04 $1.00 7 3/12/04 $11.00 8 3/16/04 $6.00 9 2/22/04 $20.00 On a different sheet: Month Amount Jan (Formula goes here, should return $9.00) Feb (Formula goes here, should return $21.00) Mar (Formula goes here, should return $17.00) That is what I am trying to do Any suggestions? -Minitman On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav" wrote: Give example of your data and expected answer. Mangesh "Minitman" wrote in message .. . Greetings, I am trying to get the sum for each month in a column which has thee entire year. So far I have: SUMIF($B:$B,{need month and year here},$DT:$DT) B is formatted as mm/dd/yy I need mm/yy in the formula. I can't remember how to do this! Anyone have any ideas? Any help would be appreciated. TIA -Minitman |
Sorry, forgot to mention that E1 contain Jan.
Put Feb in E2, and copy down the function to get value for Feb. Mangesh "Minitman" wrote in message ... Hey Mangesh, B DT 3 1/20/04 $4.00 4 1/21/04 $3.00 5 1/29/04 $2.00 6 2/14/04 $1.00 7 3/12/04 $11.00 8 3/16/04 $6.00 9 2/22/04 $20.00 On a different sheet: Month Amount Jan (Formula goes here, should return $9.00) Feb (Formula goes here, should return $21.00) Mar (Formula goes here, should return $17.00) That is what I am trying to do Any suggestions? -Minitman On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav" wrote: Give example of your data and expected answer. Mangesh "Minitman" wrote in message .. . Greetings, I am trying to get the sum for each month in a column which has thee entire year. So far I have: SUMIF($B:$B,{need month and year here},$DT:$DT) B is formatted as mm/dd/yy I need mm/yy in the formula. I can't remember how to do this! Anyone have any ideas? Any help would be appreciated. TIA -Minitman |
Another thing, in the formula, enter apr, may, ... and so on till dec.
Also the cell E1 in my formula conatins the text "Jan" and not a date formatted as MMM. If it has a date formatted as MMM, then you need to replace the CHOOSE(MONTH($B$1:$B$7),"Jan","Feb","Mar")=E1 with MONTH($B$1:$B$7)=E1 Mangesh "Mangesh Yadav" wrote in message ... =SUMPRODUCT(--(CHOOSE(MONTH($B$1:$B$7),"Jan","Feb","Mar")=E1),$D T$1:$DT$7) confirm with control shift enter Mangesh "Minitman" wrote in message ... Hey Mangesh, B DT 3 1/20/04 $4.00 4 1/21/04 $3.00 5 1/29/04 $2.00 6 2/14/04 $1.00 7 3/12/04 $11.00 8 3/16/04 $6.00 9 2/22/04 $20.00 On a different sheet: Month Amount Jan (Formula goes here, should return $9.00) Feb (Formula goes here, should return $21.00) Mar (Formula goes here, should return $17.00) That is what I am trying to do Any suggestions? -Minitman On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav" wrote: Give example of your data and expected answer. Mangesh "Minitman" wrote in message .. . Greetings, I am trying to get the sum for each month in a column which has thee entire year. So far I have: SUMIF($B:$B,{need month and year here},$DT:$DT) B is formatted as mm/dd/yy I need mm/yy in the formula. I can't remember how to do this! Anyone have any ideas? Any help would be appreciated. TIA -Minitman |
Minitman wrote:
Hey Mangesh, B DT 3 1/20/04 $4.00 4 1/21/04 $3.00 5 1/29/04 $2.00 6 2/14/04 $1.00 7 3/12/04 $11.00 8 3/16/04 $6.00 9 2/22/04 $20.00 On a different sheet: Month Amount Jan (Formula goes here, should return $9.00) Feb (Formula goes here, should return $21.00) Mar (Formula goes here, should return $17.00) That is what I am trying to do Any suggestions? -Minitman On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav" wrote: Give example of your data and expected answer. Mangesh "Minitman" wrote in message . .. Greetings, I am trying to get the sum for each month in a column which has thee entire year. So far I have: SUMIF($B:$B,{need month and year here},$DT:$DT) B is formatted as mm/dd/yy I need mm/yy in the formula. I can't remember how to do this! Anyone have any ideas? Any help would be appreciated. TIA -Minitman Why don't you try the "non-sense" I proposed... Under Month enter: 1-Jan-04 1-Feb-02 etc. Format these month cells as mmm-yy. Supposing that the data on Sheet1 and 1-Jan-04 under Month is in A2, invoke in B2 faster: =SUMIF(Sheet1!B:B,"="&A2,Sheet1!DT:DT)-SUMIF(Sheet1!B:B,""&EOMONTH(A2,0),Sheet1!DT:DT) and copy down. -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
Sorry, I do not understand what is happening with this code.
I did try it and the result is somewhat different then expected. In my real sheet, the expected total is $181.50 the result with this code after converting it is $1678.75. Since I do not understand what is going on, I am not sure where to begin to debug it. Any ideas? Here is my converted version: =SUMIF('[Payroll Checks.xls]2003'!$C:$C,"="&A9,'[Payroll Checks.xls]2003'!$DT:$DT)-SUMIF('[Payroll Checks.xls]2003'!$C:$C,""&EOMONTH(A9,0),'[Payroll Checks.xls]2003'!$DT:$DT) Payroll Checks is a separate workbook with a sheet called 2003. A9 is in the workbook of interest instead of A2. C:C is the column of interest instead of B:B. otherwise I simply cut and paste the formula into F9 where I need the monthly totals. Any help would be appreciated. TIA -Minitman On Mon, 13 Jun 2005 08:41:18 +0200, Aladin Akyurek wrote: Minitman wrote: Hey Mangesh, B DT 3 1/20/04 $4.00 4 1/21/04 $3.00 5 1/29/04 $2.00 6 2/14/04 $1.00 7 3/12/04 $11.00 8 3/16/04 $6.00 9 2/22/04 $20.00 On a different sheet: Month Amount Jan (Formula goes here, should return $9.00) Feb (Formula goes here, should return $21.00) Mar (Formula goes here, should return $17.00) That is what I am trying to do Any suggestions? -Minitman On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav" wrote: Give example of your data and expected answer. Mangesh "Minitman" wrote in message ... Greetings, I am trying to get the sum for each month in a column which has thee entire year. So far I have: SUMIF($B:$B,{need month and year here},$DT:$DT) B is formatted as mm/dd/yy I need mm/yy in the formula. I can't remember how to do this! Anyone have any ideas? Any help would be appreciated. TIA -Minitman Why don't you try the "non-sense" I proposed... Under Month enter: 1-Jan-04 1-Feb-02 etc. Format these month cells as mmm-yy. Supposing that the data on Sheet1 and 1-Jan-04 under Month is in A2, invoke in B2 faster: =SUMIF(Sheet1!B:B,"="&A2,Sheet1!DT:DT)-SUMIF(Sheet1!B:B,""&EOMONTH(A2,0),Sheet1!DT:DT) and copy down. |
Minitman wrote:
Sorry, I do not understand what is happening with this code. I did try it and the result is somewhat different then expected. In my real sheet, the expected total is $181.50 the result with this code after converting it is $1678.75. Since I do not understand what is going on, I am not sure where to begin to debug it. Any ideas? Here is my converted version: =SUMIF('[Payroll Checks.xls]2003'!$C:$C,"="&A9,'[Payroll Checks.xls]2003'!$DT:$DT)-SUMIF('[Payroll Checks.xls]2003'!$C:$C,""&EOMONTH(A9,0),'[Payroll Checks.xls]2003'!$DT:$DT) Payroll Checks is a separate workbook with a sheet called 2003. A9 is in the workbook of interest instead of A2. C:C is the column of interest instead of B:B. otherwise I simply cut and paste the formula into F9 where I need the monthly totals. Any help would be appreciated. TIA -Minitman On Mon, 13 Jun 2005 08:41:18 +0200, Aladin Akyurek wrote: Minitman wrote: Hey Mangesh, B DT 3 1/20/04 $4.00 4 1/21/04 $3.00 5 1/29/04 $2.00 6 2/14/04 $1.00 7 3/12/04 $11.00 8 3/16/04 $6.00 9 2/22/04 $20.00 On a different sheet: Month Amount Jan (Formula goes here, should return $9.00) Feb (Formula goes here, should return $21.00) Mar (Formula goes here, should return $17.00) That is what I am trying to do Any suggestions? -Minitman On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav" wrote: Give example of your data and expected answer. Mangesh "Minitman" wrote in message m... Greetings, I am trying to get the sum for each month in a column which has thee entire year. So far I have: SUMIF($B:$B,{need month and year here},$DT:$DT) B is formatted as mm/dd/yy I need mm/yy in the formula. I can't remember how to do this! Anyone have any ideas? Any help would be appreciated. TIA -Minitman Why don't you try the "non-sense" I proposed... Under Month enter: 1-Jan-04 1-Feb-02 etc. Format these month cells as mmm-yy. Supposing that the data on Sheet1 and 1-Jan-04 under Month is in A2, invoke in B2 faster: =SUMIF(Sheet1!B:B,"="&A2,Sheet1!DT:DT)-SUMIF(Sheet1!B:B,""&EOMONTH(A2,0),Sheet1!DT:DT) and copy down. The SumIf formula, that is,... =SUMIF('[Payroll Checks.xls]2003'!$C:$C,"="&A9,'[Payroll Checks.xls]2003'!$DT:$DT)-SUMIF('[Payroll Checks.xls]2003'!$C:$C,""&EOMONTH(A9,0),'[Payroll Checks.xls]2003'!$DT:$DT) requires that Payroll Checks.xls is open. The following works also with that file closed and should produce the same result: =SUMPRODUCT((DATE(YEAR('[Payroll Checks.xls]2003'!$C$2:$C$10),MONTH('[Payroll Checks.xls]2003'!$C$2:$C$10),1)=A9)+0,'[Payroll Checks.xls]2003'!$DT$2:$DT$10) Recall that A9 must be a date in the form of 1-Mar-05, that's a first day date of the month/year of interest. -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
| All times are GMT +1. The time now is 03:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com