How to return a BLANK if we have a BLANK cell using a COUNTIFS formula

Filippo

This formula is currently working, and it is returning how many items are reviewed for each "Project Lead".

The first part of the formula is basically saying that we would count them, as long as we have a "Reviewer" name added in the "Reviewer" column.

=IFERROR(COUNTIFS(Reviewer:Reviewer, <>"", [Project Lead]:[Project Lead], [Project Lead]@row), "")

The problem is that if I have NO "Reviewer" listed in the "Reviewer" field, and NO "Project Lead" listed in the "Project Lead" row, it returns "0".

I would like to update the same formula, that if the "Project Lead" field is BLANK, to return a BLANK value in the count and NOT "0" as it does today.

Lucas Rayala

@Filippo, the most inelegant but dead simple thing to do is this:

=IF(IFERROR(COUNTIFS(Reviewer:Reviewer, <>"", [Project Lead]:[Project Lead], [Project Lead]@row), "")=0, "", IFERROR(COUNTIFS(Reviewer:Reviewer, <>"", [Project Lead]:[Project Lead], [Project Lead]@row), ""))

Simplified, it's just this:

=IF(<your formula> = 0, "", <your formula>)

Filippo

Very clever Lucas! Simple IF statement logic. It works perfectly, thanks!!!

Lucas Rayala

@Filippo, the most inelegant but dead simple thing to do is this:

=IF(IFERROR(COUNTIFS(Reviewer:Reviewer, <>"", [Project Lead]:[Project Lead], [Project Lead]@row), "")=0, "", IFERROR(COUNTIFS(Reviewer:Reviewer, <>"", [Project Lead]:[Project Lead], [Project Lead]@row), ""))

Simplified, it's just this:

=IF(<your formula> = 0, "", <your formula>)

Filippo

Very clever Lucas! Simple IF statement logic. It works perfectly, thanks!!!