COUNTIF / AND functions
 

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" wrote in message
...
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 / AND functions
 

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" wrote in message
...
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" wrote in message
...
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 / AND functions
 

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" wrote in message
...
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" wrote in message
...
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 / AND functions
 

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?

COUNTIF / AND functions
 

Try a formula like

=SUMPRODUCT(--(A1:A5=1),--(B1:B5=1),--(C1:C5=3))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"dlund" wrote in message
...
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?