Remember Me?

 Rainy Posts: n/a 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

 Duke Carey Posts: n/a =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

 Rainy Posts: n/a 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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Rochelle B Excel Worksheet Functions 4 May 2nd 05 12:52 AM GrandCentral Excel Worksheet Functions 9 March 31st 05 09:30 PM SUB-ZERO Excel Worksheet Functions 3 January 23rd 05 11:35 PM DMB Excel Worksheet Functions 3 January 9th 05 05:47 PM Robert AS Excel Worksheet Functions 4 December 2nd 04 11:49 AM

All times are GMT +1. The time now is 09:20 PM. Copyright ©2004-2019 ExcelBanter.