sumif column of month are the same

  • Thread starter Thread starter crapit
  • Start date Start date
C

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
 
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
 
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
 
Your formula did really work. But what if on the same spreadsheet, similar
data but on another year, how is it going to work?
 
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
 
Thanks its work, but any website that provide good explanation on the --, 0+
or 1* as well, 2
 
Back
Top