Formula Help - please
 

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

Formula Help - please
 

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)


--

Regards,

Peo Sjoblom




"Anthony" wrote in message
...
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

Formula Help - please
 

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

"Peo Sjoblom" wrote:

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)


--

Regards,

Peo Sjoblom




"Anthony" wrote in message
...
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

Formula Help - please
 

Thanks for the feedback

Peo


"Anthony" wrote in message
...
Peo,
many thanks, the first of your suggestions worked just fine
rgds

"Peo Sjoblom" wrote:

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)


--

Regards,

Peo Sjoblom




"Anthony" wrote in message
...
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