COUNTIFS - Multiple Columns if blank by person

Adriane Price

I am looking to count by the person if they have not entered data in the cells within a column.

If person A is missing six (6) cells within three (3) different columns then the calculation should only calculate that person one (1) time. I have created helper columns to calculate how many people have missed entering data. Previous to my helper columns I created a formula in the sheet summary (info below) in hopes I could do it that way.

HELPER COLUMNS:

I am using this formula in my helper columns but the count is incorrect:

=COUNTIFS([Project End Date]@row, <=TODAY()) + COUNTIFS([Reason for Termination]@row, "") + COUNTIFS([Service Type]@row, "") + COUNTIFS(PRM@row, <>"")

For example, the count should reflect 0 since this person is not missing any data.

In this example, this count should reflect 1 since the person is missing data.

My goal is to be able to identify how many people have missing data within these three (3) columns. If it were only these two (2) people then it should calculate it as one (1) person is missing data.

SHEET SUMMARY:

I have attempted to count via the sheet summary but that was not working either. I would much prefer to use the sheet summary since it would make it easier for my widgets on the dashboard.

Formula:

=COUNTIFS(PRM:PRM, <>"", [Service Type]:[Service Type], "", [Project End Date]:[Project End Date], <=TODAY(), [Reason for Termination]:[Reason for Termination], "")

Looking for some assistance, please. Thank you for taking the time to review.

Paul Newcome

Put this in your helper column:

=IF(OR([Reason for Termination]@row = "", [Service Type]@row = "", [Project End Date]@row = ""), 1)

Then you should be able to use this to get a count of how many rows are missing data in one or more of the three fields:

=SUM([Helper Column]:[Helper Column])

Paul Newcome

Put this in your helper column:

=IF(OR([Reason for Termination]@row = "", [Service Type]@row = "", [Project End Date]@row = ""), 1)

Then you should be able to use this to get a count of how many rows are missing data in one or more of the three fields:

=SUM([Helper Column]:[Helper Column])

Adriane Price

@Paul Newcome - it works as expected however I forgot to mention and add in my variable that if it is owned by this person from this column (PRM) then it should show one (1), and the LM column should be blank, and vice a versa.

For example if in the PRM column there is a name then by default it would be the PRM owning it otherwise the LM will own it.

In this screenshot, you will see that this particular missing data is owned by the PRM which shows one (1) in the PRM Owned Missing Data, but it is also showing one (1) in the LM Owned Missing Data and I would like it to be blank.

When I did the =SUM([Helper Column]:[Helper Column]) this calculated all of the fields.

The formula was used in the sheet summary: =SUM([PRM Owned Missing Data]:[PRM Owned Missing Data]) and provided me with 2,906 instead of the total four (4) PRMs that have missing data.

From a report I created for management, this is what I should be receiving; four (4) total PRMs have missing data.

(screenshot - report)

The filters (screenshot) I have set up in the report to get the data above (screenshot - report)