Formula Help - please

[Guest]

Hi all

I want to sum the numbers in 'stats' worksheet column S for every line where
the date is equal to cell D1 in 'Monthlystats' worksheet.
Cell D1 is formulated just to show month/year ie 12006 for Jan2006.
So each time a cell in column A of 'stats' worksheet os found to have the
same value as D1 in 'Monthlystats' worksheet, count the number in the
corresponding line in column S.

I have this formula, but it is returning a zero value

=SUMIF(Stats!A8:A427,D1,Stats!S8:S427)

the 'result' will be shown in cell E8 of the Monthlystats worksheet.

Can anybody point out where I am going wrong, and/or suggest a better formula.

Thanks

 

[Peo Sjoblom]

If the date values in the Stats sheet are numbers looking like 12006 even if
you look in the formula bar (if they look like dates in the formula bar
disregard this) then you can use

=SUMIF(Stats!A8:A427,--TEXT(D1,"myyyy"),Stats!S8:S427)

if they are real dates like in D1 but you only want to sum if the month and
year are the same

=SUMPRODUCT(--(MONTH(Stats!A8:A427)=MONTH(D1)),--(YEAR(Stats!A8:A427)=YEAR(D
1)),Stats!S8:S427)

 

[Guest]

Peo,
many thanks, the first of your suggestions worked just fine
rgds

 

[Peo Sjoblom]

Thanks for the feedback

Peo

Peo,
many thanks, the first of your suggestions worked just fine
rgds