| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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. |
|
#4
|
|||
|
|||
|
Not in the same year in my case.
"KL" ¼¶¼g©ó¶l¥ó·s»D ... 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 |
|
#5
|
|||
|
|||
|
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. |
|
#6
|
|||
|
|||
|
The second formula is the one I wanted, thanks a lot~
"KL" ¼¶¼g©ó¶l¥ó·s»D ... 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 |
|
#8
|
|||
|
|||
|
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" ¼¶¼g©ó¶l¥ó·s»D ... 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" ¼¶¼g©ó¶l¥ó·s»D ... 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 |
|
#9
|
|||
|
|||
|
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. |
|
#10
|
|||
|
|||
|
"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),"")) |
|
#11
|
|||
|
|||
|
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" ¼¶¼g©ó¶l¥ó·s»D ... 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" ¼¶¼g©ó¶l¥ó·s»D ... 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 |
|
#12
|
|||
|
|||
|
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. |
|
#13
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculate total interest earned on a loan and monthly payment | Excel Worksheet Functions | |||
| Weekly and Monthly Subtotals | Excel Discussion (Misc queries) | |||
| show in a excel graphic a total percentage for 5 diff data fiels | Charts and Charting in Excel | |||
| Total remaining formula | Excel Worksheet Functions | |||
| Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions | |||
Linear Mode
