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.

=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

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.