Nesting AND inside the COUNTIF Function

[suejay]

Should I be able to nest the AND function inside the
COUNTIF function to include more than one range? I get
an error msg. when I try to include more than one range
in the countif function??? Help, please!

 

[Frank Kabel]

Hi
you can use only one condition within COUNTIF. If you need more
conditions use SUMPRODUCT. See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Aladin Akyurek]

What do you want to count in which ranges?

 

[Suejay]

I want to count a specific "text" response, like "yes"
or "no". Hope that's clear.
Thanks!

 

[Aladin Akyurek]

=COUNTIF(Range,"Yes")

=COUNTIF(Range,"No")

I want to count a specific "text" response, like "yes"
or "no". Hope that's clear.
Thanks!

 

[Frank Kabel]

Hi
not really
please post some example rows of your data (plain text - no attachments
please) and describe your expected result

 

[hgrove]

Aladin Akyurek wrote...

=COUNTIF(Range,"Yes")

=COUNTIF(Range,"No")

...

One interpretation of what the OP has written. Others would be

=COUNTIF(RangeA,"Yes")+COUNTIF(RangeB,"Yes")

or

=SUMPRODUCT((RangeA="Yes")*(RangeB="Yes")

 

[suejay]

=COUNTIF(RangeA,"Yes")+COUNTIF(RangeB,"Yes") works
perfectly!

Many Thanks!

 

[Gary Thomson]

Or:

Define a name for the combined range:

Highlight the first range, then hold Control and highlight
the second range.

now hit Alt+F3 and type a name in for your range.

In the Countif statement, hit F3 and paste the name in in
place of the "Range" and the function will work.

Happy to help,

Gary Thomson
Edinburgh, Scotland

 

[hgrove]

Gary Thomson wrote...

Or:

Define a name for the combined range: ...
In the Countif statement, hit F3 and paste the name in in place
of the "Range" and the function will work.

Happy to help,

...

If you want to help, test your suggestions.

COUNTIF and SUMIF don't support multiple area ranges. A formula like

=COUNTIF((A1:A6,C1:C6),">3")

will ALWAYS return #VALUE! Even if you name (A1:A6,C1:C6) XYZ, th
formula

=COUNTIF(XYZ,">3")

will also ALWAYS return #VALUE