Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Hold the Onions
 
Posts: n/a
Default How do you reference another cell in the criteria of a SUMIF funct

I want to use the following function: SUMIF(a2:a31,<=a1,b2:b31), but excel
requires the criteria to be text as follows:
SUMIF(sheet2!a1:z1,"<=a1",a2:z2). The result is that it does not see a1 as a
cell reference, but it sees a1 as "a1". In my case, a2 to a+ are the dates
of the year and a1 is to be the "thru date". I want to be able to enter any
date in cell a1 and return the total of columns b, c, d, etc. thru that date.
Obviously, the formula works if I enter
SUMIF(sheet2!a1:z1,"<=6/30/05",a2:z2), but my spreadsheet has numerous sheets
with numerous columns, so having to manually change the formulas each day
will be tedious for me and beyond the capabilities of those I have entering
the raw data. I could also vlookup a running total column, but I need
month-to-date, quarter-to-date, and year-to-date figures, so that would
essentially quadruple the size of the spreadsheet and make it substantially
more complicated. I would welcome any suggestions or another approach to
this problem. Thank you.
  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

=SUMIF(a2:a31,"<="&a1,b2:b31)
or
=SUMIF(sheet2!a1:z1,"<="&date(2005,6,30),a2:z2)
(I like unambiguous dates!)




Hold the Onions wrote:

I want to use the following function: SUMIF(a2:a31,<=a1,b2:b31), but excel
requires the criteria to be text as follows:
SUMIF(sheet2!a1:z1,"<=a1",a2:z2). The result is that it does not see a1 as a
cell reference, but it sees a1 as "a1". In my case, a2 to a+ are the dates
of the year and a1 is to be the "thru date". I want to be able to enter any
date in cell a1 and return the total of columns b, c, d, etc. thru that date.
Obviously, the formula works if I enter
SUMIF(sheet2!a1:z1,"<=6/30/05",a2:z2), but my spreadsheet has numerous sheets
with numerous columns, so having to manually change the formulas each day
will be tedious for me and beyond the capabilities of those I have entering
the raw data. I could also vlookup a running total column, but I need
month-to-date, quarter-to-date, and year-to-date figures, so that would
essentially quadruple the size of the spreadsheet and make it substantially
more complicated. I would welcome any suggestions or another approach to
this problem. Thank you.


--

Dave Peterson
  #3   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

SUMIF(a2:a31,"<="&a1,b2:b31)

Hold the Onions wrote:
I want to use the following function: SUMIF(a2:a31,<=a1,b2:b31), but excel
requires the criteria to be text as follows:
SUMIF(sheet2!a1:z1,"<=a1",a2:z2). The result is that it does not see a1 as a
cell reference, but it sees a1 as "a1". In my case, a2 to a+ are the dates
of the year and a1 is to be the "thru date". I want to be able to enter any
date in cell a1 and return the total of columns b, c, d, etc. thru that date.
Obviously, the formula works if I enter
SUMIF(sheet2!a1:z1,"<=6/30/05",a2:z2), but my spreadsheet has numerous sheets
with numerous columns, so having to manually change the formulas each day
will be tedious for me and beyond the capabilities of those I have entering
the raw data. I could also vlookup a running total column, but I need
month-to-date, quarter-to-date, and year-to-date figures, so that would
essentially quadruple the size of the spreadsheet and make it substantially
more complicated. I would welcome any suggestions or another approach to
this problem. Thank you.

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 convert text string into a cell reference Dave Davis Excel Discussion (Misc queries) 4 May 18th 23 11:48 AM
Tab name / reference cell? Maxwell-5000 Excel Worksheet Functions 0 August 8th 05 07:40 PM
Problem with formulas changing cell reference janicesweet Excel Discussion (Misc queries) 1 August 2nd 05 06:23 PM
dynamic cell reference within a text string gvm Excel Worksheet Functions 4 July 25th 05 02:40 AM
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. [email protected] Excel Worksheet Functions 2 December 11th 04 01:05 AM


All times are GMT +1. The time now is 07:57 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"