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)?
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.