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

=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

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