Ask Your Question
0

Countifs for AND, nor OR

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

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.

=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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

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

Lupp gravatar image

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

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.

ask56350CountIfs001.ods

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 511 times

Last updated: Aug 19 '15