sumif column of month are the same

[crapit]

a b c d
1
2 12-Jul-08 Sat 2.5
3 20-Jul-08 Sun 9
4 29-Jul-08 Tue 4
5 25-Aug-08 Mon 1.75
6 27-Aug-08 Wed 9.5
7 02-Sep-08 Tue 9.5
8 03-Sep-08 Wed 3.5
9 08-Sep-08 Mon 6
10 14-Sep-08 Sun 9.25
11 15-Sep-08 Mon 1.25
12 19-Sep-08 Fri 5.5



I try to sum the value if the month is the same but fail, Using cell d2
=IF(MONTH(a2)=MONTH(a1),SUMIF(a:a,MONTH(a2),c:c),"") and copy all way down

 

[Peo Sjoblom]

=SUMPRODUCT(--(MONTH(A2:A1000)=MONTH(A2)),C2:C1000)

--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

Oops, add the IF function as well


=IF(MONTH(A2)=MONTH(A1),SUMPRODUCT(--(MONTH($A$2:$A$1000)=MONTH(A2)),$C$2:$C$1000),"")


Mind you there will be a strange looking formula column

--


Regards,


Peo Sjoblom

 

[crapit]

What does the -- after sumproduct( represents?

=SUMPRODUCT(--(MONTH(A2:A1000)=MONTH(A2)),C2:C1000)

--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

Just a way to force sumproduct to use it's native format using commas and
remove value error

You can use 0+ or 1* as well, 2 minuses happens to be a nano second faster
<g>

They are a cause of misunderstanding though, I just saw someone posting a
formula using both unary minuses and multiplying as well (using *). The
whole idea is that when you use the minuses each range in the formula is
delimited by commas whereas another way of using SUMPRODUCT in these type of
non array entered array formulas
is to use it like this.


=SUMPRODUCT((MONTH(A2:A1000)=MONTH(A2))*(C2:C1000))

--


Regards,


Peo Sjoblom

 

[crapit]

Your formula did really work. But what if on the same spreadsheet, similar
data but on another year, how is it going to work?

 

[Peo Sjoblom]

If you want separate years you need to add year to it


=SUMPRODUCT(--(MONTH(A2:A1000)=MONTH(A2)),--(YEAR(A2:A1000)=YEAR(A2)),C2:C1000)

--


Regards,


Peo Sjoblom

 

[crapit]

Thanks its work, but any website that provide good explanation on the --, 0+
or 1* as well, 2