Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 17th 05, 06:27 PM
Rainy
 
Posts: n/a
Default 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   Report Post  
Old May 17th 05, 06:35 PM
Duke Carey
 
Posts: n/a
Default

=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   Report Post  
Old May 18th 05, 07:10 PM
Rainy
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I nest these 3 IF functions? Rochelle B Excel Worksheet Functions 4 May 2nd 05 12:52 AM
Why not nest more than 7 functions in Excel formula??? GrandCentral Excel Worksheet Functions 9 March 31st 05 09:30 PM
# of Functions per cell SUB-ZERO Excel Worksheet Functions 3 January 23rd 05 10:35 PM
How can I nest more than seven functions in MS Excel? DMB Excel Worksheet Functions 3 January 9th 05 04:47 PM
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. Robert AS Excel Worksheet Functions 4 December 2nd 04 10:49 AM


All times are GMT +1. The time now is 01:01 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017