Sum data for weeks in a month

[Eloise]

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

 

[Adilson Soledade]

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)

 

[Eloise]

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]

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.

 

[Eloise]

OK, that makes sense now and I got it to work. Thanks for walking me through
the pieces.