Using OR & AND in COUNTIFS

excel formula conditional-statements
Sammy picture Sammy · May 14, 2014 · Viewed 207k times · Source

I would like to include an "AND" condition for one of the conditions I have in my COUNTIFS clause.

Something like this:

=COUNTIFS(A1:A196;{"Yes"or "NO"};J1:J196;"Agree")

So, it should return the number of rows where:

 (A1:A196 is either "yes" or "no") AND (J1:j196 is "agree")

Answer

tmoore82 picture tmoore82 · May 14, 2014

You could just add a few COUNTIF statements together:

=COUNTIF(A1:A196,"yes")+COUNTIF(A1:A196,"no")+COUNTIF(J1:J196,"agree")

This will give you the result you need.

EDIT

Sorry, misread the question. Nicholas is right that the above will double count. I wasn't thinking of the AND condition the right way. Here's an alternative that should give you the correct results, which you were pretty close to in the first place:

=SUM(COUNTIFS(A1:A196,{"yes","no"},J1:J196,"agree"))

Related questions

Multiple conditions in Excel conditional formatting

I'm fairly new to Excel and so not very experienced, but I'm trying to get an entire row to highlight red based on 2 other cells, using conditional formatting. The requirements are for the corresponding M cell in the row to …

Read More
How to turn a string formula into a "real" formula

I have 0,4*A1 in a cell (as a string). How can convert this "string formula" into a real formula and calculate its value, in another cell?

Read More
Fill formula down till last row in column

I'm trying to draw down the formula that's in cell M3 to the end of the data set. I'm using column L as my base to determine the last cell with data. My formula is a concatenation of two cells …

Read More