Home |
Search |
Today's Posts |
#1
|
|||
|
|||
how do I nest functions
I am trying to sum a column of data only if it meets the "IF" criteria, but
for some reason it is summing the whole column, even if it doesn't meet the "IF" criteria. I have a worksheet with daily data, column A is dates, example: 7/12/2004 etc... I have another worksheet named "by pay week" using formulas to extract the totals of certain columns from the daily worksheet. My formula is: =if('daily data'!$a$2:$a$2000<='by pay week'!a2, sum('daily data'!$e$2:$e$2000),0) a2 is equal to the end date of the pay period. The formula is working but instead of summing the data for the dates that are less than or equal to a2 it is summing all the data in the column. Can anyone help me with this? -- Rainy |
#2
|
|||
|
|||
=sumif('daily data'!$a$2:$a$2000,"<="&'by pay week'!a2,'daily
data'!$e$2:$e$2000) "Rainy" wrote: I am trying to sum a column of data only if it meets the "IF" criteria, but for some reason it is summing the whole column, even if it doesn't meet the "IF" criteria. I have a worksheet with daily data, column A is dates, example: 7/12/2004 etc... I have another worksheet named "by pay week" using formulas to extract the totals of certain columns from the daily worksheet. My formula is: =if('daily data'!$a$2:$a$2000<='by pay week'!a2, sum('daily data'!$e$2:$e$2000),0) a2 is equal to the end date of the pay period. The formula is working but instead of summing the data for the dates that are less than or equal to a2 it is summing all the data in the column. Can anyone help me with this? -- Rainy |
#3
|
|||
|
|||
Duke,
Thank you for your help, that formula did work, but then I ran into another problem that I didn't think about. The problem is as the pay week increases and I use the same formula the less than or equal to the date will keep adding on the old data too, is there a way around this, or do I have to keep modifying the formula? I tried modifying the formula you sent me, but it doesn't work, here is what I tried: sumif('daily data'!$a$2:$a$2000,"<<"&'by pay week'!a2&"<=a2+7", 'daily data'!$e$2:$e$2000) since the end pay week is 7 days from the previous one, I didn't receive an error, but it is not working like I hoped it would. I am also trying to count 2 columns, but not count blanks: if('daily data'!$a$2000<="by pay week'!a2, counta('daily data'!$c$2:$c$2000)+counta('daily data'!$d$2:$d$2000).0) The above formula does the same and counts both columns without taking criteria into account. Was is the purpose of the "<="& in the sumif formula you sent me? -- Rainy "Rainy" wrote: I am trying to sum a column of data only if it meets the "IF" criteria, but for some reason it is summing the whole column, even if it doesn't meet the "IF" criteria. I have a worksheet with daily data, column A is dates, example: 7/12/2004 etc... I have another worksheet named "by pay week" using formulas to extract the totals of certain columns from the daily worksheet. My formula is: =if('daily data'!$a$2:$a$2000<='by pay week'!a2, sum('daily data'!$e$2:$e$2000),0) a2 is equal to the end date of the pay period. The formula is working but instead of summing the data for the dates that are less than or equal to a2 it is summing all the data in the column. Can anyone help me with this? -- Rainy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I nest these 3 IF functions? | Excel Worksheet Functions | |||
Why not nest more than 7 functions in Excel formula??? | Excel Worksheet Functions | |||
# of Functions per cell | Excel Worksheet Functions | |||
How can I nest more than seven functions in MS Excel? | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |