Hi
I have a spreadsheet with this strucure and I want to fill cells that have
sums of the entries in each year

A B
20/09/08 100
31/08/08 200
15/09/08 150

need a cell with the total of all values in column B in the month of 09/08
then 08/08 etc

total would be 250

Please help


--
NumbersDevon

=SUMPRODUCT((Year(A1:A100)=2008)*((MONTH(A1:A100)= 8)+(MONTH(A1:A100)=9))*B1:B100
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"NumbersDevon" wrote in message
...
Hi
I have a spreadsheet with this strucure and I want to fill cells that have
sums of the entries in each year

A B
20/09/08 100
31/08/08 200
15/09/08 150

need a cell with the total of all values in column B in the month of 09/08
then 08/08 etc

total would be 250

Please help


--
NumbersDevon

Hi

Put this in a cell and drag down to get the sum of each month in 2008

=SUMPRODUCT((MONTH($A$1:$A$10)=ROW(A1))*(YEAR($A$1 :$A$10)=2008)*($B$1:$B$10))

Mike

"NumbersDevon" wrote:

Hi
I have a spreadsheet with this strucure and I want to fill cells that have
sums of the entries in each year

A B
20/09/08 100
31/08/08 200
15/09/08 150

need a cell with the total of all values in column B in the month of 09/08
then 08/08 etc

total would be 250

Please help


--
NumbersDevon

You could do it this way:

=SUMPRODUCT(--(TEXT(A$1:A$10,"mmm-yy")="Sep-08"),B$1:B$10)

Adjust the ranges to suit. For August just change "Sep-08" to
"Aug-08".

Hope this helps.

Pete

On Nov 25, 1:51*pm, NumbersDevon
wrote:
Hi
I have a spreadsheet with this strucure and I want to fill cells that have
sums of the entries in each year

A * * * * * * * * * * * * * B * * * * * * * * * * * *
20/09/08 * * * * * *100
31/08/08 * * * * * *200
15/09/08 * * * * * *150

need a cell with the total of all values in column B in the month of 09/08
then 08/08 *etc

total would be 250

Please help *

--
NumbersDevon

Sort your data, then use Subtotal


"NumbersDevon" wrote:

Hi
I have a spreadsheet with this strucure and I want to fill cells that have
sums of the entries in each year

A B
20/09/08 100
31/08/08 200
15/09/08 150

need a cell with the total of all values in column B in the month of 09/08
then 08/08 etc

total would be 250

Please help


--
NumbersDevon