Re: COUNTIF & Criteria

Tech-Archive recommends: Fix windows errors by optimizing your registry

I am not sure I understand, but maybe

=SUM(COUNTIF(Revised!$A$2:$A$336,">="&G2:G336))-SUM(COUNTIF(Revised!$A$2:$A$
336,">"&I2:I336))

needs to be array entered with ctrl + shift & enter




--

Regards,

Peo Sjoblom

"David Lipetz" <dlipetz@xxxxxxxxxxx> wrote in message
news:uv%23cKlnAGHA.272@xxxxxxxxxxxxxxxxxxxxxxx
> Thanks Peo! That worked, but now I have another problem.
>
> I wanted to create this as an array formula so that the criteria is
selected
> from each row: G2 & I2, G3 & I3, and so forth. With the & in front of the
> cell reference, it uses that same reference throughout the array.
>
>
> "Peo Sjoblom" <terre08@xxxxxxxx> wrote in message
> news:%23sFhmgnAGHA.3976@xxxxxxxxxxxxxxxxxxxxxxx
> > Try
> >
> >
=COUNTIF(Revised!$A$2:$A$336,">="&G2)-COUNTIF(Revised!$A$2:$A$336,">"&I2)
> >
> > otherwise excel sees it as you are looking for the text G2 and I2
> >
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > "David Lipetz" <dlipetz@xxxxxxxxxxx> wrote in message
> > news:e27TFenAGHA.4080@xxxxxxxxxxxxxxxxxxxxxxx
> >> I'm using the SUMIF function to add numbers in a range that meet a
given
> >> criteria. The criteria I need to use is a range of numbers (>=1 and
<=5,
> >>=6
> >> and <=10, etc).
> >>
> >> While I have been able to perform this calculation by hard coding in
the
> >> parameters (">=1" and ">5" for example), I am unable to complete the
> > formula
> >> if I want to use cell references for the criteria.
> >>
> >> Here is the whole formula that currently works by hard coding in the
> >> criteria:
> >>
> >> =COUNTIF(Revised!$A$2:$A$336,">=1")-COUNTIF(Revised!$A$2:$A$336,">5")
> >>
> >> In other words, I want to use >=G2 rather than >=1 and use >I2 rather
> >> than
> >> >5.
> >>
> >> How do I accomplish this?
> >>
> >> Thanks,
> >> David
> >>
> >>
> >
> >
>
>


.

Relevant Pages

  • Re: value of #s in column if between dates
    ... No need to array enter it ... > format and didn't know how. ... >> Regards, ... >> Peo Sjoblom ...
    (microsoft.public.excel.worksheet.functions)
  • Re: How can I generate number based on two measurements?
    ... "Peo Sjoblom" wrote: ... > Actually you don't have to array enter this formula ... >> your prices are in B2:T13. ...
    (microsoft.public.excel.worksheet.functions)
  • RE: countif
    ... Regards, ... Peo Sjoblom ... "Brad" wrote: ... > a cell reference ...
    (microsoft.public.excel.misc)
  • Re: Unexpected (?) behaviour of OFFSET() in array formulas
    ... same behaviour as offset where to be able to evaluate the array you need to ... Regards, ... I searched in .misc with keywords: OFFSET evaluation Peo Sjoblom ...
    (microsoft.public.excel.misc)
  • Re: COUNTIF & Criteria
    ... >> I wanted to create this as an array formula so that the criteria is ... >> cell reference, it uses that same reference throughout the array. ... >>> Peo Sjoblom ...
    (microsoft.public.excel)