Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 26
Default Can I set the criteria in a sumif statement as a cell reference?

I am trying to use the sumif statement in excel and want to set the criteria
to a "less than" date. The only way I can get the formula to work is by
hardcoding the date into the sumif statement i.e
=sumif(A1:II1,"<31-OCT-05",A2:II2). However I want to copy this formula using
a number of different dates and do not want to individually hard code the
date. I want to replace the hard coded date with a cell reference that
contains the date. When I do this I get the microsoft excel pop up box
telling me there is an error in my formula.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 104
Default Can I set the criteria in a sumif statement as a cell reference?

Hi Duncan,

How about =SUMPRODUCT(--(A2:A4F1),B2:B4) where A2:A4 contain the dates, F1
contains the referenced date cell and B2:B4 the values.
--
Thanks,
MarkN


"Duncan" wrote:

I am trying to use the sumif statement in excel and want to set the criteria
to a "less than" date. The only way I can get the formula to work is by
hardcoding the date into the sumif statement i.e
=sumif(A1:II1,"<31-OCT-05",A2:II2). However I want to copy this formula using
a number of different dates and do not want to individually hard code the
date. I want to replace the hard coded date with a cell reference that
contains the date. When I do this I get the microsoft excel pop up box
telling me there is an error in my formula.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Can I set the criteria in a sumif statement as a cell reference?


I used this formula

=SUMIF(A1:A9,D2,B1:B9)

Where Cell D2 was formatted as a date and I input "<2/27/1991" into
that cell just as it appears without the quotes and the sum worked.


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=565424

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default Can I set the criteria in a sumif statement as a cell reference?

Where B1 contains the date:
=sumif(A1:II1,"<"&B1,A2:II2)


"Duncan" wrote:

I am trying to use the sumif statement in excel and want to set the criteria
to a "less than" date. The only way I can get the formula to work is by
hardcoding the date into the sumif statement i.e
=sumif(A1:II1,"<31-OCT-05",A2:II2). However I want to copy this formula using
a number of different dates and do not want to individually hard code the
date. I want to replace the hard coded date with a cell reference that
contains the date. When I do this I get the microsoft excel pop up box
telling me there is an error in my formula.

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
Maintaining cell reference after sorting GRITS Excel Discussion (Misc queries) 2 April 30th 23 07:42 PM
Highlighted Reference Cell for a Working Cell Mr. Low Excel Worksheet Functions 0 June 22nd 06 04:27 PM
Help with this conditional IF statement C-Dawg Excel Discussion (Misc queries) 3 May 15th 06 06:01 PM
Urgent date/scheduling calc needed jct Excel Worksheet Functions 3 February 24th 06 02:36 AM
Could the "Criteria" in COUNTIF function be a cell reference? JohnSheenWSN Excel Worksheet Functions 2 June 16th 05 08:07 PM


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

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

About Us

"It's about Microsoft Excel"