|
monthly total
Dear all,
I have a worksheet as below 5/1/2005 50 23/1/2005 100 18/1/2005 120 2/2/2005 12 12/2/2005 24 4/3/2005 15 I want to sum up the total of each month to get Jan 2005 270 Feb 2005 36 Mar 2005 15 What should be the formula? thanks.... ims |
Hi Turk,
If all data belong to the same year you could try the following formula to get the sum for January: =SUMPRODUCT(--(MONTH($A$1:$A$6)=1),$B$1:$B$6) otherwise: =SUMPRODUCT(--(MONTH($A$1:$A$6)=1),--(YEAR($A$1:$A$6)=2005),$B$1:$B$6) or =SUMPRODUCT(--(TEXT($A$1:$A$6,"mmyy")="0105"),$B$1:$B$6) etc. You can also replace =1 with a reference to the cell that contains the number of the month, e.g. =SUMPRODUCT(--(MONTH($A$1:$A$6)=D1),$B$1:$B$6) Regards, KL "Turk" wrote in message ... Dear all, I have a worksheet as below 5/1/2005 50 23/1/2005 100 18/1/2005 120 2/2/2005 12 12/2/2005 24 4/3/2005 15 I want to sum up the total of each month to get Jan 2005 270 Feb 2005 36 Mar 2005 15 What should be the formula? thanks.... ims |
Let A2:B7 house the sample you provided.
In column C from C2 on, enter the first day dates of the month/year combinations: 1-Jan-2005 1-Feb-2005 etc. In D2 enter & copy down: =SUMIF($A$2:$A$7,"="&C2,$B$2:$B$7)-SUMIF($A$2:$A$7,""&EOMONTH(C2,0),$B$2:$B$7) Turk wrote: Dear all, I have a worksheet as below 5/1/2005 50 23/1/2005 100 18/1/2005 120 2/2/2005 12 12/2/2005 24 4/3/2005 15 I want to sum up the total of each month to get Jan 2005 270 Feb 2005 36 Mar 2005 15 What should be the formula? thanks.... ims -- [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. |
Not in the same year in my case.
"KL" 级糶秎ン穝籇 ... Hi Turk, If all data belong to the same year you could try the following formula to get the sum for January: =SUMPRODUCT(--(MONTH($A$1:$A$6)=1),$B$1:$B$6) otherwise: =SUMPRODUCT(--(MONTH($A$1:$A$6)=1),--(YEAR($A$1:$A$6)=2005),$B$1:$B$6) or =SUMPRODUCT(--(TEXT($A$1:$A$6,"mmyy")="0105"),$B$1:$B$6) etc. You can also replace =1 with a reference to the cell that contains the number of the month, e.g. =SUMPRODUCT(--(MONTH($A$1:$A$6)=D1),$B$1:$B$6) Regards, KL "Turk" wrote in message ... Dear all, I have a worksheet as below 5/1/2005 50 23/1/2005 100 18/1/2005 120 2/2/2005 12 12/2/2005 24 4/3/2005 15 I want to sum up the total of each month to get Jan 2005 270 Feb 2005 36 Mar 2005 15 What should be the formula? thanks.... ims |
The formula seems has some problems, can only get correct results for May
and June as below. A B C D 2005/3/1 12 2005/1/1 193 2005/3/4 24 2005/2/1 193 2005/3/17 36 2005/3/1 193 2005/4/2 25 2005/4/1 121 2005/4/6 50 2005/5/1 46 2005/5/8 46 2005/6/1 0 "Aladin Akyurek" .. . Let A2:B7 house the sample you provided. In column C from C2 on, enter the first day dates of the month/year combinations: 1-Jan-2005 1-Feb-2005 etc. In D2 enter & copy down: =SUMIF($A$2:$A$7,"="&C2,$B$2:$B$7)-SUMIF($A$2:$A$7,""&EOMONTH(C2,0),$B$2:$ B$7) Turk wrote: Dear all, I have a worksheet as below 5/1/2005 50 23/1/2005 100 18/1/2005 120 2/2/2005 12 12/2/2005 24 4/3/2005 15 I want to sum up the total of each month to get Jan 2005 270 Feb 2005 36 Mar 2005 15 What should be the formula? thanks.... ims -- [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. |
The second formula is the one I wanted, thanks a lot~
"KL" 级糶秎ン穝籇 ... Hi Turk, If all data belong to the same year you could try the following formula to get the sum for January: =SUMPRODUCT(--(MONTH($A$1:$A$6)=1),$B$1:$B$6) otherwise: =SUMPRODUCT(--(MONTH($A$1:$A$6)=1),--(YEAR($A$1:$A$6)=2005),$B$1:$B$6) or =SUMPRODUCT(--(TEXT($A$1:$A$6,"mmyy")="0105"),$B$1:$B$6) etc. You can also replace =1 with a reference to the cell that contains the number of the month, e.g. =SUMPRODUCT(--(MONTH($A$1:$A$6)=D1),$B$1:$B$6) Regards, KL "Turk" wrote in message ... Dear all, I have a worksheet as below 5/1/2005 50 23/1/2005 100 18/1/2005 120 2/2/2005 12 12/2/2005 24 4/3/2005 15 I want to sum up the total of each month to get Jan 2005 270 Feb 2005 36 Mar 2005 15 What should be the formula? thanks.... ims |
I have changed my mind to use the 3rd formula, with little modification.
By entering 2005-01 at H1 and coping down the column, =SUMPRODUCT(--(TEXT($A$1:$A$6,"yyyy-mm")=H1),$B$1:$B$6) So user friendly! Thanks again...... "Don Guillett" 级糶秎ン穝籇 ... To add a bit instead of editing for 1, 2, 3 you could have used row(a1) and copied down -- Don Guillett SalesAid Software "Turk" wrote in message ... The second formula is the one I wanted, thanks a lot~ "KL" 级糶秎ン穝籇 ... Hi Turk, If all data belong to the same year you could try the following formula to get the sum for January: =SUMPRODUCT(--(MONTH($A$1:$A$6)=1),$B$1:$B$6) otherwise: =SUMPRODUCT(--(MONTH($A$1:$A$6)=1),--(YEAR($A$1:$A$6)=2005),$B$1:$B$6) or =SUMPRODUCT(--(TEXT($A$1:$A$6,"mmyy")="0105"),$B$1:$B$6) etc. You can also replace =1 with a reference to the cell that contains the number of the month, e.g. =SUMPRODUCT(--(MONTH($A$1:$A$6)=D1),$B$1:$B$6) Regards, KL "Turk" wrote in message ... Dear all, I have a worksheet as below 5/1/2005 50 23/1/2005 100 18/1/2005 120 2/2/2005 12 12/2/2005 24 4/3/2005 15 I want to sum up the total of each month to get Jan 2005 270 Feb 2005 36 Mar 2005 15 What should be the formula? thanks.... ims |
Make sure that the dates are true dates, not just looking as dates. A
diganostic test would be: =COUNT(DateRange)=SUMPRODUCT(ISNUMBER(DateRange+0) +0) If the result is not TRUE, then some or all of your dates are text-formatted. Turk wrote: The formula seems has some problems, can only get correct results for May and June as below. A B C D 2005/3/1 12 2005/1/1 193 2005/3/4 24 2005/2/1 193 2005/3/17 36 2005/3/1 193 2005/4/2 25 2005/4/1 121 2005/4/6 50 2005/5/1 46 2005/5/8 46 2005/6/1 0 "Aladin Akyurek" .. . Let A2:B7 house the sample you provided. In column C from C2 on, enter the first day dates of the month/year combinations: 1-Jan-2005 1-Feb-2005 etc. In D2 enter & copy down: =SUMIF($A$2:$A$7,"="&C2,$B$2:$B$7)-SUMIF($A$2:$A$7,""&EOMONTH(C2,0),$B$2:$ B$7) Turk wrote: Dear all, I have a worksheet as below 5/1/2005 50 23/1/2005 100 18/1/2005 120 2/2/2005 12 12/2/2005 24 4/3/2005 15 I want to sum up the total of each month to get Jan 2005 270 Feb 2005 36 Mar 2005 15 What should be the formula? thanks.... ims -- [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. -- [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. |
"Aladin Akyurek" wrote...
Make sure that the dates are true dates, not just looking as dates. A diganostic test would be: =COUNT(DateRange)=SUMPRODUCT(ISNUMBER(DateRange+0 )+0) .... If we're talking dates pasted from HTML tables with leading or trailing nonbreaking spaces (decimal char code 160), adding 0 to them will return errors, in which case your COUNT and SUMPRODUCT calls would return the same values. More robust to use the array formula =COUNT(DataRange)=COUNT(-SUBSTITUTE(DateRange,CHAR(160),"")) |
If you like it, that's great but you really don't need to add a col...
-- Don Guillett SalesAid Software "Turk" wrote in message ... I have changed my mind to use the 3rd formula, with little modification. By entering 2005-01 at H1 and coping down the column, =SUMPRODUCT(--(TEXT($A$1:$A$6,"yyyy-mm")=H1),$B$1:$B$6) So user friendly! Thanks again...... "Don Guillett" 级糶秎ン穝籇 ... To add a bit instead of editing for 1, 2, 3 you could have used row(a1) and copied down -- Don Guillett SalesAid Software "Turk" wrote in message ... The second formula is the one I wanted, thanks a lot~ "KL" 级糶秎ン穝籇 ... Hi Turk, If all data belong to the same year you could try the following formula to get the sum for January: =SUMPRODUCT(--(MONTH($A$1:$A$6)=1),$B$1:$B$6) otherwise: =SUMPRODUCT(--(MONTH($A$1:$A$6)=1),--(YEAR($A$1:$A$6)=2005),$B$1:$B$6) or =SUMPRODUCT(--(TEXT($A$1:$A$6,"mmyy")="0105"),$B$1:$B$6) etc. You can also replace =1 with a reference to the cell that contains the number of the month, e.g. =SUMPRODUCT(--(MONTH($A$1:$A$6)=D1),$B$1:$B$6) Regards, KL "Turk" wrote in message ... Dear all, I have a worksheet as below 5/1/2005 50 23/1/2005 100 18/1/2005 120 2/2/2005 12 12/2/2005 24 4/3/2005 15 I want to sum up the total of each month to get Jan 2005 270 Feb 2005 36 Mar 2005 15 What should be the formula? thanks.... ims |
Yeah~
SOme of them are in text format, thanks! "Aladin Akyurek" .. . Make sure that the dates are true dates, not just looking as dates. A diganostic test would be: =COUNT(DateRange)=SUMPRODUCT(ISNUMBER(DateRange+0) +0) If the result is not TRUE, then some or all of your dates are text-formatted. Turk wrote: The formula seems has some problems, can only get correct results for May and June as below. A B C D 2005/3/1 12 2005/1/1 193 2005/3/4 24 2005/2/1 193 2005/3/17 36 2005/3/1 193 2005/4/2 25 2005/4/1 121 2005/4/6 50 2005/5/1 46 2005/5/8 46 2005/6/1 0 "Aladin Akyurek" .. . Let A2:B7 house the sample you provided. In column C from C2 on, enter the first day dates of the month/year combinations: 1-Jan-2005 1-Feb-2005 etc. In D2 enter & copy down: =SUMIF($A$2:$A$7,"="&C2,$B$2:$B$7)-SUMIF($A$2:$A$7,""&EOMONTH(C2,0),$B$2:$ B$7) Turk wrote: Dear all, I have a worksheet as below 5/1/2005 50 23/1/2005 100 18/1/2005 120 2/2/2005 12 12/2/2005 24 4/3/2005 15 I want to sum up the total of each month to get Jan 2005 270 Feb 2005 36 Mar 2005 15 What should be the formula? thanks.... ims -- [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. -- [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. |
Convert them to true dates. SumIf formula (which is faster its
SumProduct equivalent) will work as advertised. Turk wrote: Yeah~ SOme of them are in text format, thanks! "Aladin Akyurek" .. . Make sure that the dates are true dates, not just looking as dates. A diganostic test would be: =COUNT(DateRange)=SUMPRODUCT(ISNUMBER(DateRange+ 0)+0) If the result is not TRUE, then some or all of your dates are text-formatted. Turk wrote: The formula seems has some problems, can only get correct results for May and June as below. A B C D 2005/3/1 12 2005/1/1 193 2005/3/4 24 2005/2/1 193 2005/3/17 36 2005/3/1 193 2005/4/2 25 2005/4/1 121 2005/4/6 50 2005/5/1 46 2005/5/8 46 2005/6/1 0 "Aladin Akyurek" l... Let A2:B7 house the sample you provided. In column C from C2 on, enter the first day dates of the month/year combinations: 1-Jan-2005 1-Feb-2005 etc. In D2 enter & copy down: =SUMIF($A$2:$A$7,"="&C2,$B$2:$B$7)-SUMIF($A$2:$A$7,""&EOMONTH(C2,0),$B$2:$ B$7) Turk wrote: Dear all, I have a worksheet as below 5/1/2005 50 23/1/2005 100 18/1/2005 120 2/2/2005 12 12/2/2005 24 4/3/2005 15 I want to sum up the total of each month to get Jan 2005 270 Feb 2005 36 Mar 2005 15 What should be the formula? thanks.... ims -- [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. -- [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. -- [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 04:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com