SUMIF

[rhhince]

I have 2 columns. Column B that has dates from 2000 to 2010. Another
column J that has profits or losses. The columns are about 20000 rows.
I was wanting to sum column J in degrees of 6 months each using some
sort of simple formula without manually going down the column and
searching for 6 months of dates and then summing column J. Any ideas.
Thanks.

 

[Harald Staff]

A Pivot table will do this in no time. Set the dates as row field and group
it by months, six, and profit/loss as Value. Ensure that the value field
says Sum, not Count.

HTH. Best wishes Harald

 

[Don Guillett Excel MVP]

A Pivot table will do this in no time. Set the dates as row field and group
it by months, six, and profit/loss as Value. Ensure that the value field
says Sum, not Count.

HTH. Best wishes Harald






- Show quoted text -

A macror solution where checksA is your datecol and checksD is your
value column

Sub SumByDateGroupsSAS()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set ss = Sheet1
Set ds = Sheet7
ds.Columns(2).Clear
r = 2
fy = Year(ss.Cells(7, 1))
ly = Year(ss.Cells(Rows.Count, 1).End(xlUp))

For i = fy To ly
For j = 1 To 12

ds.Cells(r, 2).Value = _
Evaluate("SUMPRODUCT(--(year(checksa)=" & i & ")" _
& ",--(month(checksa)=" & j & " ),checksd)")

r = r + 1
Next j
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub