COUNTIF / AND functions

How do I use the COUNTIF and the AND functions together? I tried using the
following formula and got an error:

=COUNTIF(and((B4:B27,">=9 am"),(B4:B27,"<=10 am")))
--
Thanks.

Brian

Hi,
Use the SUMPRODUCT function:
SUMPRODUCT( (criteria1) * (criteria2) * ... (range_to_sum_or_just_1) )
To count:
assuming 9 Am is in B1 and 10 Am is in C1:
=SUMPRODUCT(($B$4:$B$27>=$B$1)*($C$4:$C$27<=$C$1)*1)

--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"brianwakin" wrote:

> How do I use the COUNTIF and the AND functions together? I tried using the
> following formula and got an error:
>
> =COUNTIF(and((B4:B27,">=9 am"),(B4:B27,"<=10 am")))
> --
> Thanks.
>
> Brian

=COUNTIF(B4:B27,">=9:00 am")-Countif(B4:B27,">10:00 am")

--
Regards,
Tom Ogilvy


"brianwakin" <brianwakin@discussions.microsoft.com> wrote in message
news:04E5FA21-7BA4-472F-9BC8-79865AC52DD9@microsoft.com...
> How do I use the COUNTIF and the AND functions together? I tried using
the
> following formula and got an error:
>
> =COUNTIF(and((B4:B27,">=9 am"),(B4:B27,"<=10 am")))
> --
> Thanks.
>
> Brian

I see what you are trying to do here, but it doesn't work for my application
(the 9AM and 10AM was just an example). Is there any way to use AND/OR with
COUNTIF? Thanks.
--
Brian


"Tom Ogilvy" wrote:

> =COUNTIF(B4:B27,">=9:00 am")-Countif(B4:B27,">10:00 am")
>
> --
> Regards,
> Tom Ogilvy
>
>
> "brianwakin" <brianwakin@discussions.microsoft.com> wrote in message
> news:04E5FA21-7BA4-472F-9BC8-79865AC52DD9@microsoft.com...
> > How do I use the COUNTIF and the AND functions together? I tried using
> the
> > following formula and got an error:
> >
> > =COUNTIF(and((B4:B27,">=9 am"),(B4:B27,"<=10 am")))
> > --
> > Thanks.
> >
> > Brian
>
>
>

It works fine for me using time values. did you format the cell as number
or general instead of time. are your values really just time values.

This is the general solution and works in all cases (times, dates, numbers,
text - any place a single countif would produce an accurate count if there
is only one criteria) when properly written.

Countif/sumif don't work with AND/OR. Maybe you would feel more comfortable
with the slower sumproduct approach offered by Sebastion.

--
Regards,
Tom Ogilvy

"brianwakin" <brianwakin@discussions.microsoft.com> wrote in message
news:72A6D5F6-8D21-40F8-9DBF-DBB0A0E7A089@microsoft.com...
> I see what you are trying to do here, but it doesn't work for my
application
> (the 9AM and 10AM was just an example). Is there any way to use AND/OR
with
> COUNTIF? Thanks.
> --
> Brian
>
>
> "Tom Ogilvy" wrote:
>
> > =COUNTIF(B4:B27,">=9:00 am")-Countif(B4:B27,">10:00 am")
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "brianwakin" <brianwakin@discussions.microsoft.com> wrote in message
> > news:04E5FA21-7BA4-472F-9BC8-79865AC52DD9@microsoft.com...
> > > How do I use the COUNTIF and the AND functions together? I tried
using
> > the
> > > following formula and got an error:
> > >
> > > =COUNTIF(and((B4:B27,">=9 am"),(B4:B27,"<=10 am")))
> > > --
> > > Thanks.
> > >
> > > Brian
> >
> >
> >

I figured out how to make it work using your solution. At first the formula
was returning the wrong values but I made a few changes and it works well
now. Thanks a lot for the help. You saved me a lot of time trying to do it
the "wrong" way.
--
Brian


"Tom Ogilvy" wrote:

> It works fine for me using time values. did you format the cell as number
> or general instead of time. are your values really just time values.
>
> This is the general solution and works in all cases (times, dates, numbers,
> text - any place a single countif would produce an accurate count if there
> is only one criteria) when properly written.
>
> Countif/sumif don't work with AND/OR. Maybe you would feel more comfortable
> with the slower sumproduct approach offered by Sebastion.
>
> --
> Regards,
> Tom Ogilvy
>
> "brianwakin" <brianwakin@discussions.microsoft.com> wrote in message
> news:72A6D5F6-8D21-40F8-9DBF-DBB0A0E7A089@microsoft.com...
> > I see what you are trying to do here, but it doesn't work for my
> application
> > (the 9AM and 10AM was just an example). Is there any way to use AND/OR
> with
> > COUNTIF? Thanks.
> > --
> > Brian
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > =COUNTIF(B4:B27,">=9:00 am")-Countif(B4:B27,">10:00 am")
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "brianwakin" <brianwakin@discussions.microsoft.com> wrote in message
> > > news:04E5FA21-7BA4-472F-9BC8-79865AC52DD9@microsoft.com...
> > > > How do I use the COUNTIF and the AND functions together? I tried
> using
> > > the
> > > > following formula and got an error:
> > > >
> > > > =COUNTIF(and((B4:B27,">=9 am"),(B4:B27,"<=10 am")))
> > > > --
> > > > Thanks.
> > > >
> > > > Brian
> > >
> > >
> > >
>
>
>

what if you have 3 columns to check?

with an array like

1 1 3
2 1 2
2 1 4
1 2 3
1 1 3

How do you count the number of "1 1 3" occurances?