Can I use a Reference inside a SUMIF or COUNTIF Function?

I am trying to utilize a SUMIF function that references a cell to provide the
the criteria, so that I can changed the criteria inside a optimization or
scenario. My functions are all returning zero when I use the cell reference,
but when I use the actual value, it returns what it should? Any way I can
get around this?

It should work properly if you are looking for an equal condition.
However, if you are looking for >,<,or <>, then you need something like

=SUMIF(CritRange,">"&A1,SumRange)

where your criterion value is in A1, if you are looking for an
inequality. You put whatever inequality sign you need between the
quotes.

HTH

DOR

On Sun, 13 Nov 2005 13:50:06 -0800, "Albie" <[email protected]>
wrote:

>I am trying to utilize a SUMIF function that references a cell to provide the
>the criteria, so that I can changed the criteria inside a optimization or
>scenario. My functions are all returning zero when I use the cell reference,
>but when I use the actual value, it returns what it should? Any way I can
>get around this?

It's just a syntax issue:

=SUMIF(rng_to_check, "=" & cell_ref, rng_to_sum)




--ron

Thanks! Works wonders!

"Ron Rosenfeld" wrote:

> On Sun, 13 Nov 2005 13:50:06 -0800, "Albie" <[email protected]>
> wrote:
>
> >I am trying to utilize a SUMIF function that references a cell to provide the
> >the criteria, so that I can changed the criteria inside a optimization or
> >scenario. My functions are all returning zero when I use the cell reference,
> >but when I use the actual value, it returns what it should? Any way I can
> >get around this?
>
> It's just a syntax issue:
>
> =SUMIF(rng_to_check, "=" & cell_ref, rng_to_sum)
>
>
>
>
> --ron
>

Thanks! Works great. I was suspecting syntax but as usual, the help files
didn't help

"DOR" wrote:

> It should work properly if you are looking for an equal condition.
> However, if you are looking for >,<,or <>, then you need something like
>
> =SUMIF(CritRange,">"&A1,SumRange)
>
> where your criterion value is in A1, if you are looking for an
> inequality. You put whatever inequality sign you need between the
> quotes.
>
> HTH
>
> DOR
>
>