Sum?

[stone]

Hi guys.

Column A contains alot of dates, column B alot of values. (see sample on picture)


[img]Hi guys.

Column A contains alot of dates, column B alot of values. (see sample on picture)




What i need to do in column F is to sum the values in column B for each month.

That is the formula in column F2, should do somthing like, sum all values in column B if the dates are something(some day).2.2002

Hope you understand what Im trying to do.
Can you help?

BK
Stulli



[ This Message was edited by: stone on 2002-05-24 08:52 ]

[Jay Petrulis]

I prefer using SUMPRODUCT in these cases, although there are numerous other ways, some more efficient than others.

=SUMPRODUCT((MONTH(A1:A10)=1)*(B1:B10))

would give you the sum of the January data, for example.

HTH,
Jay

[Ian Mac]

On 2002-05-24 08:28, Jay Petrulis wrote:
I prefer using SUMPRODUCT in these cases, although there are numerous other ways, some more efficient than others.

=SUMPRODUCT((MONTH(A1:A10)=1)*(B1:B10))

would give you the sum of the January data, for example.

HTH,
Jay

Just an extention,

=SUMPRODUCT((MONTH(A1:A10)=ROW(1:1))*(B1:B10))

can be dragged down to fill the other months.

[stone]

THANX FOR YOUR RESPOSE jAY and Ian.

but what about the year 2002, what if some date in column A is x.x.2003?, I only want to sum the months of 2002. (can I somehow add the year in the formula you gave me?)

BK
Stulli

[ This Message was edited by: stone on 2002-05-24 08:37 ]

[IML]

staying with the sumproduct route, you could something along the lines of
=SUMPRODUCT((TEXT($A$1:$A$10,"m-yy")=(ROW(1:1)&"-02"))*($B$1:$B$10))

for Jan-02 specifically. change the "-02" to suit your year of interest. good luck

[Jay Petrulis]

On 2002-05-24 08:49, IML wrote:
staying with the sumproduct route, you could something along the lines of
=SUMPRODUCT((TEXT($A$1:$A$10,"m-yy")=(ROW(1:1)&"-02"))*($B$1:$B$10))

for Jan-02 specifically. change the "-02" to suit your year of interest. good luck

IML, clever! I wouldn't think of that option. Nice job.

=SUMPRODUCT((MONTH(A1:A10)=ROW(1:1))*(YEAR(A1:A10)=2003)*(B1:B10))

would be another way. You could also make the year constant dynamic, or use other operators <, <=, >=, >=, <>, etc. It is quite flexible.

Bye,
Jay