SUMIF help- 'criteria' doesn't allow formulas?

The SUMIF Function, as I understand it, works as SUMIF(range,"criteria",[sum_range]) where the range and the sum range are actual cells.

My question refers to the criteria argument in the SUMIF function.

I want to sum a column of cells only if a different column of cells is beyond a certain date. For example, I have a table which, among other things, shows large sales and the date sold. (There are always less than 1000 total records, so it's not terribly large). I want to sum all sales 30 days PRIOR to today. So, if today is 7/28/04, I want to sum all sales PRIOR to 6/28/04.

The way I am doing this now (which is probably terrible) involves using a temporary field which reads =TODAY()-30. This always yields the date that I want, because it is exactly one month ago. I have this formula in cell B2 of a different sheet.

I write my SUMIF function as follows: =SUMIF(M1:M625,"=Sheet2!B2",H1:H625). This does not work. BUT, if I write it like this: =SUMIF(M1:M625,"=6/28/04",H1:H625), it works perfectly. Apparently the "criteria" parameter will not accept formulas or even references to other cells. I tried typing 6/28/04 into cell B2, but also no luck, even if done on the same worksheet. The problem is that I want to automate this (preferably into a macro), and having to identify the date and input it each time is a real pain.

Is there any way around this lack of a feature?

Thanks so much!

J,

Try:

=SUMIF(M1:M625,"=" & Sheet2!B2,H1:H625)

HTH,
Bernie
MS Excel MVP

"JAnderson" wrote in message
...
The SUMIF Function, as I understand it, works as
SUMIF(range,"criteria",[sum_range]) where the range and the sum range are
actual cells.

My question refers to the criteria argument in the SUMIF function.

I want to sum a column of cells only if a different column of cells is
beyond a certain date. For example, I have a table which, among other
things, shows large sales and the date sold. (There are always less than
1000 total records, so it's not terribly large). I want to sum all sales 30
days PRIOR to today. So, if today is 7/28/04, I want to sum all sales PRIOR
to 6/28/04.

The way I am doing this now (which is probably terrible) involves using a
temporary field which reads =TODAY()-30. This always yields the date that I
want, because it is exactly one month ago. I have this formula in cell B2
of a different sheet.

I write my SUMIF function as follows:
=SUMIF(M1:M625,"=Sheet2!B2",H1:H625). This does not work. BUT, if I write
it like this: =SUMIF(M1:M625,"=6/28/04",H1:H625), it works perfectly.
Apparently the "criteria" parameter will not accept formulas or even
references to other cells. I tried typing 6/28/04 into cell B2, but also no
luck, even if done on the same worksheet. The problem is that I want to
automate this (preferably into a macro), and having to identify the date and
input it each time is a real pain.

Is there any way around this lack of a feature?

Thanks so much!

Yes - instead of using ,"=Sheet2!B2"use
"="&Sheet2!B2
So the whole formula is:
=SUMIF(M1:M625,"="&Sheet2!B2,H1:H625)
....
Using ,"=Sheet2!B2" has Excel compare against the string of characters
,=,S,h,e, ... etc. not the sheet!B2 but the LETTERS in "Sheet2!..."
--
Bob Umlas
Excel MVP

"JAnderson" wrote in message
...
The SUMIF Function, as I understand it, works as
SUMIF(range,"criteria",[sum_range]) where the range and the sum range are
actual cells.

My question refers to the criteria argument in the SUMIF function.

I want to sum a column of cells only if a different column of cells is
beyond a certain date. For example, I have a table which, among other
things, shows large sales and the date sold. (There are always less than
1000 total records, so it's not terribly large). I want to sum all sales 30
days PRIOR to today. So, if today is 7/28/04, I want to sum all sales PRIOR
to 6/28/04.

The way I am doing this now (which is probably terrible) involves using a
temporary field which reads =TODAY()-30. This always yields the date that I
want, because it is exactly one month ago. I have this formula in cell B2
of a different sheet.

I write my SUMIF function as follows:
=SUMIF(M1:M625,"=Sheet2!B2",H1:H625). This does not work. BUT, if I write
it like this: =SUMIF(M1:M625,"=6/28/04",H1:H625), it works perfectly.
Apparently the "criteria" parameter will not accept formulas or even
references to other cells. I tried typing 6/28/04 into cell B2, but also no
luck, even if done on the same worksheet. The problem is that I want to
automate this (preferably into a macro), and having to identify the date and
input it each time is a real pain.

Is there any way around this lack of a feature?

Thanks so much!

Bernie & Bob,

Thank you so much, you're both lifesavers! Works like a charm!

Jason

"Bob Umlas" wrote:

Yes - instead of using ,"=Sheet2!B2"use
"="&Sheet2!B2
So the whole formula is:
=SUMIF(M1:M625,"="&Sheet2!B2,H1:H625)
....
Using ,"=Sheet2!B2" has Excel compare against the string of characters
,=,S,h,e, ... etc. not the sheet!B2 but the LETTERS in "Sheet2!..."
--
Bob Umlas
Excel MVP

"JAnderson" wrote in message
...
The SUMIF Function, as I understand it, works as
SUMIF(range,"criteria",[sum_range]) where the range and the sum range are
actual cells.

My question refers to the criteria argument in the SUMIF function.

I want to sum a column of cells only if a different column of cells is
beyond a certain date. For example, I have a table which, among other
things, shows large sales and the date sold. (There are always less than
1000 total records, so it's not terribly large). I want to sum all sales 30
days PRIOR to today. So, if today is 7/28/04, I want to sum all sales PRIOR
to 6/28/04.

The way I am doing this now (which is probably terrible) involves using a
temporary field which reads =TODAY()-30. This always yields the date that I
want, because it is exactly one month ago. I have this formula in cell B2
of a different sheet.

I write my SUMIF function as follows:
=SUMIF(M1:M625,"=Sheet2!B2",H1:H625). This does not work. BUT, if I write
it like this: =SUMIF(M1:M625,"=6/28/04",H1:H625), it works perfectly.
Apparently the "criteria" parameter will not accept formulas or even
references to other cells. I tried typing 6/28/04 into cell B2, but also no
luck, even if done on the same worksheet. The problem is that I want to
automate this (preferably into a macro), and having to identify the date and
input it each time is a real pain.

Is there any way around this lack of a feature?

Thanks so much!

You can use cell references in SUMIF functions, but they are outside of the
quotes and must be concatenated with the operator contained in the quotes.
Try using this:

=SUMIF(M1:M625,"="&Sheet2!B2,H1:H625)

--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh

"JAnderson" wrote in message
...
The SUMIF Function, as I understand it, works as
SUMIF(range,"criteria",[sum_range]) where the range and the sum range are
actual cells.

My question refers to the criteria argument in the SUMIF function.

I want to sum a column of cells only if a different column of cells is
beyond a certain date. For example, I have a table which, among other
things, shows large sales and the date sold. (There are always less than
1000 total records, so it's not terribly large). I want to sum all sales 30
days PRIOR to today. So, if today is 7/28/04, I want to sum all sales PRIOR
to 6/28/04.

The way I am doing this now (which is probably terrible) involves using a
temporary field which reads =TODAY()-30. This always yields the date that I
want, because it is exactly one month ago. I have this formula in cell B2
of a different sheet.

I write my SUMIF function as follows:
=SUMIF(M1:M625,"=Sheet2!B2",H1:H625). This does not work. BUT, if I write
it like this: =SUMIF(M1:M625,"=6/28/04",H1:H625), it works perfectly.
Apparently the "criteria" parameter will not accept formulas or even
references to other cells. I tried typing 6/28/04 into cell B2, but also no
luck, even if done on the same worksheet. The problem is that I want to
automate this (preferably into a macro), and having to identify the date and
input it each time is a real pain.

Is there any way around this lack of a feature?

Thanks so much!