# Countifs for AND, nor OR

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.

=COUNTIFS(Raw.$F$2:$F$169,$B3,Raw.$I$2:$I$169,C$2)

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 close merge delete

Sort by » oldest newest most voted

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.