Using OR AND in COUNTIFS

Question

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

User · Answer

Using array formula    SUM COUNT IF D1 D5  Yes  COUNT D1 D5       COUNT IF D1 D5  No  COUNT D1 D5       COUNT IF D1 D5  Agree  COUNT D1 D5          PRESS   CTRL   SHIFT   ENTER

User · Answer

i found i had to do something akin to     countifs  A1 A196  yes   j1 j196   agree      countifs  A1 A196  no   j1 j196   agree

User · Answer

One solution is doing the sum    SUM COUNTIFS A1 A196   yes   no   B1 B196  agree      or know its not the countifs but the sumproduct will do it in one line    SUMPRODUCT   A1 A196   yes   no     j1 j196  agree

User · Answer

In a more general case   N  A union B    N A    N B  - N A intersect B     COUNTIFS A1 A196  Yes  J1 J196  Agree   COUNTIFS A1 A196  No  J1 J196  Agree  -A1 A196  Yes  A1 A196  No

User · Answer

There is probably a more efficient solution to your question  but following formula should do the trick    SUM COUNTIFS J1 J196  agree  A1 A196  yes   COUNTIFS J1 J196  agree  A1 A196  no

User · Answer

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

[excel] Using OR & AND in COUNTIFS

Examples related to excel

Examples related to formula

Examples related to conditional-statements