CODEWITHSUNDEEP
excelexcel-formula
Daimonic
Daimonic

Reputation: 21

blanks issue within COUNTIFS()

I am looking to establish a conditional formatting rule using COUNTIFS() that highlights entire rows when all cells within a certain row matches all cells within another row.

Heres what I've got so far which seems to work when there aren't blanks in my data set:

COUNTIFS($E$25:$E$500,$E25,$F$25:$F$500,$F25)>1

However, when there are blanks found, the function returns 0 and nothing is highlighted.

Are there ways around this exception?

Thanks

Upvotes: 2

Views: 43

Answers (1)

Axel Richter
Axel Richter

Reputation: 61870

COUNTIFS function:

If the criteria argument is a reference to an empty cell, the COUNTIFS function treats the empty cell as a 0 value.

But empty cells in a range do not equal a 0 value. So they are not counted.

To get both in same type context you can try:

=COUNTIFS($E$25:$E$500,$E25&"",$F$25:$F$500,$F25&"")>1

The concatenation of an empty cell and an empty string results in an empty string rather than a 0 value. That works using Excel 365.

Upvotes: 2

Related Questions