Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Hide formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions |