| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum data for weeks in a month
I need to add all values from each week into one sum per month.
For instance, below is some of my data as it's set up for my pivot table. A B C 1 Week DEPTID Week hrs 2 27-Dec 1000 8.00 3 3-Jan 8100 16.00 4 10-Jan 7000 1.60 5 17-Jan 7000 4.40 6 24-Jan 8100 20.00 On a separate (non pivot table) sheet, how do I show a sum by month, like this: A B 1 Dec Jan 2 8.00 42.00 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum data for weeks in a month
Try this for Jan: =SUMPRODUCT(--(MONTH(A2:A6)=1),C2:C6) and for Feb:
=SUMPRODUCT(--(MONTH(A2:A6)=2),C2:C6). You could change the numbers 1 and 2 in to formulas, by this dependens how you're entering the values. One possibility is =SUMPRODUCT(--(MONTH(A2:A6)=IF(A$1="Jan",1,2),C2:C6) -- Adilson Soledade "Eloise" wrote: I need to add all values from each week into one sum per month. For instance, below is some of my data as it's set up for my pivot table. A B C 1 Week DEPTID Week hrs 2 27-Dec 1000 8.00 3 3-Jan 8100 16.00 4 10-Jan 7000 1.60 5 17-Jan 7000 4.40 6 24-Jan 8100 20.00 On a separate (non pivot table) sheet, how do I show a sum by month, like this: A B 1 Dec Jan 2 8.00 42.00 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum data for weeks in a month
What does the "--" after SUMPRODUCT represent? I think I'm doing something
wrong because it's not working. My hours data is on a sheet called "Labor Actuals" and the monthly sum is on a sheet called "Summary" within the same document. Does this change things? My formatting is like it's listed below (d-mmm), so still not quite getting how to set the formula (and why sumproduct? I don't want to multiply, just add each week) Thanks "Adilson Soledade" wrote: Try this for Jan: =SUMPRODUCT(--(MONTH(A2:A6)=1),C2:C6) and for Feb: =SUMPRODUCT(--(MONTH(A2:A6)=2),C2:C6). You could change the numbers 1 and 2 in to formulas, by this dependens how you're entering the values. One possibility is =SUMPRODUCT(--(MONTH(A2:A6)=IF(A$1="Jan",1,2),C2:C6) -- Adilson Soledade "Eloise" wrote: I need to add all values from each week into one sum per month. For instance, below is some of my data as it's set up for my pivot table. A B C 1 Week DEPTID Week hrs 2 27-Dec 1000 8.00 3 3-Jan 8100 16.00 4 10-Jan 7000 1.60 5 17-Jan 7000 4.40 6 24-Jan 8100 20.00 On a separate (non pivot table) sheet, how do I show a sum by month, like this: A B 1 Dec Jan 2 8.00 42.00 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum data for weeks in a month
Let's go step by step:
The SUMPRODUCT function must work with numeric values. When we use the function MONTH(A1:A6)=1, the result is an array with TRUE and FALSE values. So we use the double minus signal "--" or function N or multiply by 1 to transform these in numeric values. TRUE become 1 and FALSE become 0. You must specify the origin of each range you use in the formulas. In your case, the function =SUMPRODUCT(--(MONTH(A2:A6)=1),C2:C6) became, somethig like =SUMPRODUCT(--(MONTH(Summary!A2:A6)=1),'Labor Actuals'!C2:C6) . The SUMPRODUCT function will first values 1 or 0 in the first argument, with the respective hour values in the second argument. If the month is the desired month in the first column the value is 1 and this multiplied by the number of hours in hte second column. If the month is not the desired the first column result is zero and so is the match product. So you add only the value wich satisfied the condition we stablished before. I hope I was clear. Any doubts, please be comfort to argue. -- Adilson Soledade "Eloise" wrote: What does the "--" after SUMPRODUCT represent? I think I'm doing something wrong because it's not working. My hours data is on a sheet called "Labor Actuals" and the monthly sum is on a sheet called "Summary" within the same document. Does this change things? My formatting is like it's listed below (d-mmm), so still not quite getting how to set the formula (and why sumproduct? I don't want to multiply, just add each week) Thanks "Adilson Soledade" wrote: Try this for Jan: =SUMPRODUCT(--(MONTH(A2:A6)=1),C2:C6) and for Feb: =SUMPRODUCT(--(MONTH(A2:A6)=2),C2:C6). You could change the numbers 1 and 2 in to formulas, by this dependens how you're entering the values. One possibility is =SUMPRODUCT(--(MONTH(A2:A6)=IF(A$1="Jan",1,2),C2:C6) -- Adilson Soledade "Eloise" wrote: I need to add all values from each week into one sum per month. For instance, below is some of my data as it's set up for my pivot table. A B C 1 Week DEPTID Week hrs 2 27-Dec 1000 8.00 3 3-Jan 8100 16.00 4 10-Jan 7000 1.60 5 17-Jan 7000 4.40 6 24-Jan 8100 20.00 On a separate (non pivot table) sheet, how do I show a sum by month, like this: A B 1 Dec Jan 2 8.00 42.00 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum data for weeks in a month
OK, that makes sense now and I got it to work. Thanks for walking me through
the pieces. "Adilson Soledade" wrote: Let's go step by step: The SUMPRODUCT function must work with numeric values. When we use the function MONTH(A1:A6)=1, the result is an array with TRUE and FALSE values. So we use the double minus signal "--" or function N or multiply by 1 to transform these in numeric values. TRUE become 1 and FALSE become 0. You must specify the origin of each range you use in the formulas. In your case, the function =SUMPRODUCT(--(MONTH(A2:A6)=1),C2:C6) became, somethig like =SUMPRODUCT(--(MONTH(Summary!A2:A6)=1),'Labor Actuals'!C2:C6) . The SUMPRODUCT function will first values 1 or 0 in the first argument, with the respective hour values in the second argument. If the month is the desired month in the first column the value is 1 and this multiplied by the number of hours in hte second column. If the month is not the desired the first column result is zero and so is the match product. So you add only the value wich satisfied the condition we stablished before. I hope I was clear. Any doubts, please be comfort to argue. -- Adilson Soledade "Eloise" wrote: What does the "--" after SUMPRODUCT represent? I think I'm doing something wrong because it's not working. My hours data is on a sheet called "Labor Actuals" and the monthly sum is on a sheet called "Summary" within the same document. Does this change things? My formatting is like it's listed below (d-mmm), so still not quite getting how to set the formula (and why sumproduct? I don't want to multiply, just add each week) Thanks "Adilson Soledade" wrote: Try this for Jan: =SUMPRODUCT(--(MONTH(A2:A6)=1),C2:C6) and for Feb: =SUMPRODUCT(--(MONTH(A2:A6)=2),C2:C6). You could change the numbers 1 and 2 in to formulas, by this dependens how you're entering the values. One possibility is =SUMPRODUCT(--(MONTH(A2:A6)=IF(A$1="Jan",1,2),C2:C6) -- Adilson Soledade "Eloise" wrote: I need to add all values from each week into one sum per month. For instance, below is some of my data as it's set up for my pivot table. A B C 1 Week DEPTID Week hrs 2 27-Dec 1000 8.00 3 3-Jan 8100 16.00 4 10-Jan 7000 1.60 5 17-Jan 7000 4.40 6 24-Jan 8100 20.00 On a separate (non pivot table) sheet, how do I show a sum by month, like this: A B 1 Dec Jan 2 8.00 42.00 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do you calculate the number of weeks on a month | Excel Discussion (Misc queries) | |||
| Excel 2003 month to month data change grid | Excel Discussion (Misc queries) | |||
| How do I count the number of even weeks in the current month. | Excel Worksheet Functions | |||
| Archiving 4 weeks of data into Month folders | Excel Discussion (Misc queries) | |||
| calculate weeks from a start date ( not yr weeks) | Excel Worksheet Functions | |||
Linear Mode
