Formula Help - please


G

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
 
Ad

Advertisements

P

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)
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top