CountIF but not blanks

AJ King

Hi everyone - sorry if this has already been asked and answered. But, i am trying to have a box checked if there is a duplicate entry. This is the formula i am using:

=IF(COUNTIF([FA Peoplesoft ID]:[FA Peoplesoft ID], [FA Peoplesoft ID]@row) > 1, 1, 0)

It works, but when i add the formula to the duplicate column all the way down the sheet it marks all blanks as duplicate - is there a way to exclude it counting blanks? We will be adding entries in time that we want the formula to apply to.

Thanks!

Paul Newcome

Lets start by changing your COUNTIF to a COUNTIFS and adding in some ISTEXT criteria.

=IF(COUNTIFS([FA Peoplesoft ID]:[FA Peoplesoft ID], AND(@cell = [FA Peoplesoft ID]@row, ISTEXT(@cell))) > 1, 1, 0)

Paul Newcome

Lets start by changing your COUNTIF to a COUNTIFS and adding in some ISTEXT criteria.

=IF(COUNTIFS([FA Peoplesoft ID]:[FA Peoplesoft ID], AND(@cell = [FA Peoplesoft ID]@row, ISTEXT(@cell))) > 1, 1, 0)

AJ King

Thanks Paul! This worked. I just updated it to be ISNUMBER as it will be numbers entered into that field. Thanks again!

Paul Newcome

Happy to help! 👍️

Tim Meeks

@Paul Newcome I used your formula to help me solve the same issue. Needed one extra ")" before the ">". Thanks!

Paul Newcome

@Tim Meeks Glad you were able to adapt one of my solutions, but...

My solution above would actually throw an error if you added a 4th closing parenthesis before the ">".