Can I use a Reference inside a SUMIF or COUNTIF Function? in Excel Worksheet Functions  
 |  Edit my Profile  |  Help
 
     
   
 
 
 
Albie 11/13/2005 1:49 PM PST
  Question
  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?
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
DOR 11/13/2005 2:15 PM PST
  Answer
  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

 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Albie 11/13/2005 2:20 PM PST
   
  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
>
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Ron Rosenfeld 11/13/2005 2:16 PM PST
  Answer
  On Sun, 13 Nov 2005 13:50:06 -0800, "Albie" <Albie@discussions.microsoft.com>
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
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Albie 11/13/2005 2:19 PM PST
   
  Thanks! Works wonders!

"Ron Rosenfeld" wrote:

> On Sun, 13 Nov 2005 13:50:06 -0800, "Albie" <Albie@discussions.microsoft.com>
> 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
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Laura 9/29/2009 11:54 AM PST
   
  I am trying to use a countif formula to count column a - rows 6-99 if
between10%and 25% AND if column b =DS.

can you help - i have tried the sumproduct - might not have the right
formula to put in but I tried and i have tried the function and several other
suggestions the help provided.

I would greatly appreciate it. Thanks
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
T. Valko 9/29/2009 3:50 PM PST
   
  Try this:

=SUMPRODUCT(--(A6:A99>=10%),--(A6:A99<=25%),--(B6:B99="DS"))

--
Biff
Microsoft Excel MVP


"Laura" <Laura@discussions.microsoft.com> wrote in message
news:CF07DFED-E5B7-432F-B25E-F8188C109CCE@microsoft.com...
>I am trying to use a countif formula to count column a - rows 6-99 if
> between10%and 25% AND if column b =DS.
>
> can you help - i have tried the sumproduct - might not have the right
> formula to put in but I tried and i have tried the function and several
> other
> suggestions the help provided.
>
> I would greatly appreciate it. Thanks
>


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Laura 9/30/2009 4:24 AM PST
   
  it works!!! Thank you so very much.

"T. Valko" wrote:

> Try this:
>
> =SUMPRODUCT(--(A6:A99>=10%),--(A6:A99<=25%),--(B6:B99="DS"))
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Laura" <Laura@discussions.microsoft.com> wrote in message
> news:CF07DFED-E5B7-432F-B25E-F8188C109CCE@microsoft.com...
> >I am trying to use a countif formula to count column a - rows 6-99 if
> > between10%and 25% AND if column b =DS.
> >
> > can you help - i have tried the sumproduct - might not have the right
> > formula to put in but I tried and i have tried the function and several
> > other
> > suggestions the help provided.
> >
> > I would greatly appreciate it. Thanks
> >
>
>
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
T. Valko 9/30/2009 8:51 AM PST
   
  You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Laura" <Laura@discussions.microsoft.com> wrote in message
news:E92AFFAA-4C8C-4352-BD04-D0BB45EF052D@microsoft.com...
> it works!!! Thank you so very much.
>
> "T. Valko" wrote:
>
>> Try this:
>>
>> =SUMPRODUCT(--(A6:A99>=10%),--(A6:A99<=25%),--(B6:B99="DS"))
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Laura" <Laura@discussions.microsoft.com> wrote in message
>> news:CF07DFED-E5B7-432F-B25E-F8188C109CCE@microsoft.com...
>> >I am trying to use a countif formula to count column a - rows 6-99 if
>> > between10%and 25% AND if column b =DS.
>> >
>> > can you help - i have tried the sumproduct - might not have the right
>> > formula to put in but I tried and i have tried the function and several
>> > other
>> > suggestions the help provided.
>> >
>> > I would greatly appreciate it. Thanks
>> >
>>
>>
>>


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
  Return to Microsoft Communities  Notify me of replies