Home // Blog & Excel Tips // COUNTIF Error when looking for logical signs

Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!

2. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'

 

 

COUNTIF Error when looking for logical signs

Comments Closed
Uncategorised

COUNTIF Error when looking for logical signs

COUNTIF and SUMIF are very useful features in Excel. We have come across a problem though which results in a COUNTIF error when looking for logical signs.

One of the features of the COUNTIF and SUMIF functions is to include a logical test e.g. COUNTIF a cell is < 0. Countif error

However the function experiences a problem when you try and count where the criteria is not the logical function less than ( e.g <0 ) but for the text “<” (our spreadsheet testing tool presents the maps using < to represent a copy of the previous cell). As shown in the picture, Excel has incorrectly counted the logical signs.

The Excel function seems to ignore the text and does the test instead even though there is no number after the logical. This seems to apply to all the logical functions i.e. <, >, >=, and <=.

Not sure of a solution but for a quick fix you can replace all the ‘<‘s with another character (make sure you set your FIND/ REPLACE to values and not formula) and count those.