Is it possible to create a complex COUNTIF that will count the number
of rows in a range where, for each row, column A = 100 and column B is
greater than 5?

I realise that I could create a formula cell on each line that contains
the result of the evaluation for each line and base the COUNTIF on
that. However, I don't own the spreadsheet and that operation would not
be acceptable.

Thanks in advance.

Dave

You could try this array function* in a cell somewhere:

=SUM(IF((A1:A1000=100)*(B1:B1000)>5,1,0))

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you will need to use CTRL-SHIFT-ENTER instead of
just ENTER. If you do this correctly, then Excel will wrap curly braces
{ } around the formula - you should not type these yourself.

Hope this helps - a SUMPRODUCT formula would also do this.

Pete

dave wrote:
> Is it possible to create a complex COUNTIF that will count the number
> of rows in a range where, for each row, column A = 100 and column B is
> greater than 5?
>
> I realise that I could create a formula cell on each line that contains
> the result of the evaluation for each line and base the COUNTIF on
> that. However, I don't own the spreadsheet and that operation would not
> be acceptable.
>
> Thanks in advance.
>
> Dave

=SUMPRODUCT(--(A1:A1000=100),--(B1:B1000>5))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"dave" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Is it possible to create a complex COUNTIF that will count the number
> of rows in a range where, for each row, column A = 100 and column B is
> greater than 5?
>
> I realise that I could create a formula cell on each line that contains
> the result of the evaluation for each line and base the COUNTIF on
> that. However, I don't own the spreadsheet and that operation would not
> be acceptable.
>
> Thanks in advance.
>
> Dave
>

Thanks for the help guys - I ended up using SUMPRODUCT.

BTW, why does each component require a prefix of "--" ?




Bob Phillips wrote:
> =SUMPRODUCT(--(A1:A1000=100),--(B1:B1000>5))
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "dave" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Is it possible to create a complex COUNTIF that will count the number
> > of rows in a range where, for each row, column A = 100 and column B is
> > greater than 5?
> >
> > I realise that I could create a formula cell on each line that contains
> > the result of the evaluation for each line and base the COUNTIF on
> > that. However, I don't own the spreadsheet and that operation would not
> > be acceptable.
> >
> > Thanks in advance.
> >
> > Dave
> >

Thanks for the help guys - I ended up using SUMPRODUCT.

BTW, why does each component require a prefix of "--" ?




Bob Phillips wrote:
> =SUMPRODUCT(--(A1:A1000=100),--(B1:B1000>5))
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "dave" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Is it possible to create a complex COUNTIF that will count the number
> > of rows in a range where, for each row, column A = 100 and column B is
> > greater than 5?
> >
> > I realise that I could create a formula cell on each line that contains
> > the result of the evaluation for each line and base the COUNTIF on
> > that. However, I don't own the spreadsheet and that operation would not
> > be acceptable.
> >
> > Thanks in advance.
> >
> > Dave
> >

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

dave wrote:
>
> Thanks for the help guys - I ended up using SUMPRODUCT.
>
> BTW, why does each component require a prefix of "--" ?
>
> Bob Phillips wrote:
> > =SUMPRODUCT(--(A1:A1000=100),--(B1:B1000>5))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "dave" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > Is it possible to create a complex COUNTIF that will count the number
> > > of rows in a range where, for each row, column A = 100 and column B is
> > > greater than 5?
> > >
> > > I realise that I could create a formula cell on each line that contains
> > > the result of the evaluation for each line and base the COUNTIF on
> > > that. However, I don't own the spreadsheet and that operation would not
> > > be acceptable.
> > >
> > > Thanks in advance.
> > >
> > > Dave
> > >

--

Dave Peterson

-- stuff?

We are supposed to surround it with a technical air of mystery, to make us
seem smart!

Bob

"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> =sumproduct() likes to work with numbers. The -- stuff changes trues and
falses
> to 1's and 0's.
>
> Bob Phillips explains =sumproduct() in much more detail here:
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>
> And J.E. McGimpsey has some notes at:
> http://mcgimpsey.com/excel/formulae/doubleneg.html
>
> dave wrote:
> >
> > Thanks for the help guys - I ended up using SUMPRODUCT.
> >
> > BTW, why does each component require a prefix of "--" ?
> >
> > Bob Phillips wrote:
> > > =SUMPRODUCT(--(A1:A1000=100),--(B1:B1000>5))
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > "dave" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > Is it possible to create a complex COUNTIF that will count the
number
> > > > of rows in a range where, for each row, column A = 100 and column B
is
> > > > greater than 5?
> > > >
> > > > I realise that I could create a formula cell on each line that
contains
> > > > the result of the evaluation for each line and base the COUNTIF on
> > > > that. However, I don't own the spreadsheet and that operation would
not
> > > > be acceptable.
> > > >
> > > > Thanks in advance.
> > > >
> > > > Dave
> > > >
>
> --
>
> Dave Peterson

It's the stuff that dreams are made of!

or...

Stuff that in your pipe and smoke it.

or...

Stuff the magic dragon, wait, that's not right.


Bob Phillips wrote:
>
> -- stuff?
>
> We are supposed to surround it with a technical air of mystery, to make us
> seem smart!
>
> Bob
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > =sumproduct() likes to work with numbers. The -- stuff changes trues and
> falses
> > to 1's and 0's.
> >
> > Bob Phillips explains =sumproduct() in much more detail here:
> > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> >
> > And J.E. McGimpsey has some notes at:
> > http://mcgimpsey.com/excel/formulae/doubleneg.html
> >
> > dave wrote:
> > >
> > > Thanks for the help guys - I ended up using SUMPRODUCT.
> > >
> > > BTW, why does each component require a prefix of "--" ?
> > >
> > > Bob Phillips wrote:
> > > > =SUMPRODUCT(--(A1:A1000=100),--(B1:B1000>5))
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace somewhere in email address with gmail if mailing direct)
> > > >
> > > > "dave" <(E-Mail Removed)> wrote in message
> > > > news:(E-Mail Removed)...
> > > > > Is it possible to create a complex COUNTIF that will count the
> number
> > > > > of rows in a range where, for each row, column A = 100 and column B
> is
> > > > > greater than 5?
> > > > >
> > > > > I realise that I could create a formula cell on each line that
> contains
> > > > > the result of the evaluation for each line and base the COUNTIF on
> > > > > that. However, I don't own the spreadsheet and that operation would
> not
> > > > > be acceptable.
> > > > >
> > > > > Thanks in advance.
> > > > >
> > > > > Dave
> > > > >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Don't let these guys fool you!

You *don't* need those unaries ( -- ).

This works just as well:

=SUMPRODUCT((A1:A1000=100)*(B1:B1000>5))

And with 4 *less* keystrokes.

Like they said, they like to make 'stuff' seem complicated.<bg>
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"dave" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
Thanks for the help guys - I ended up using SUMPRODUCT.

BTW, why does each component require a prefix of "--" ?




Bob Phillips wrote:
> =SUMPRODUCT(--(A1:A1000=100),--(B1:B1000>5))
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "dave" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Is it possible to create a complex COUNTIF that will count the number
> > of rows in a range where, for each row, column A = 100 and column B is
> > greater than 5?
> >
> > I realise that I could create a formula cell on each line that contains
> > the result of the evaluation for each line and base the COUNTIF on
> > that. However, I don't own the spreadsheet and that operation would not
> > be acceptable.
> >
> > Thanks in advance.
> >
> > Dave
> >

The xldynamic page on SUMPRODUCT is excellent, although MAC users be
advised that the site will crash the Safari browser. I use Firefox when
referring to it.

Jason




Dave Peterson wrote:
> =sumproduct() likes to work with numbers. The -- stuff changes trues and falses
> to 1's and 0's.
>
> Bob Phillips explains =sumproduct() in much more detail here:
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>
> And J.E. McGimpsey has some notes at:
> http://mcgimpsey.com/excel/formulae/doubleneg.html
>
> dave wrote:
>> Thanks for the help guys - I ended up using SUMPRODUCT.
>>
>> BTW, why does each component require a prefix of "--" ?
>>
>> Bob Phillips wrote:
>>> =SUMPRODUCT(--(A1:A1000=100),--(B1:B1000>5))
>>>
>>> --
>>> HTH
>>>
>>> Bob Phillips
>>>
>>> (replace somewhere in email address with gmail if mailing direct)
>>>
>>> "dave" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> Is it possible to create a complex COUNTIF that will count the number
>>>> of rows in a range where, for each row, column A = 100 and column B is
>>>> greater than 5?
>>>>
>>>> I realise that I could create a formula cell on each line that contains
>>>> the result of the evaluation for each line and base the COUNTIF on
>>>> that. However, I don't own the spreadsheet and that operation would not
>>>> be acceptable.
>>>>
>>>> Thanks in advance.
>>>>
>>>> Dave
>>>>
>