Ask Your Question

Countifs for AND, nor OR

asked 2015-08-18 20:44:18 +0200

Judgeneo gravatar image

I have two columns, I want to be able to count entries where column 1 is equal to A, and column 2 is equal to B. Right now I'm using the below formula.


However, when I add up all the answers this generates, it is greater than the number of rows in the Raw data.

It seems that countifs counts for IF Criteria A, OR Criteria B. Is there anyway of changing it so it counts for IF(Criteria A) & IF(Criteria B)?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2015-08-19 00:48:16 +0200

Lupp gravatar image

updated 2015-08-19 00:52:48 +0200

COUNTIFS is specified to combine the conditions by AND. For some strange reason there is, however, a dangerous option which may cause problems if unwittingly switched off.

Did you check the setting for 'Search criteria ... must apply to whole cells'? (See > 'Tools' > 'Options' > 'LibreOffice Calc' > 'Calculate'.)

I would anyway prefer not to use COUNTIF or COUNTIFS. Using SUMPRODUCT with the appropriate Boolean expressions on parameter positions will allow to define conditions in a very precise way.

See also the attached example.


edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-08-18 20:44:18 +0200

Seen: 1,092 times

Last updated: Aug 19 '15