COUNTIFS Error

Question

I am using a form that the employees fill out certain information. If the unit was not used that day, it changes a cell to NIU. There is a worksheet for each shift. In each, I added a column that checks the previous shifts worksheet to verify there were no blank cells for the day. I originally had this formula written:

=IF('3RD SHIFT CHECKS'!B13="NIU","NIU",IF(COUNTIFS('3RD SHIFT CHECKS'!C13:O13,"")>0,"VERIFY","PASS"))

This did not return any errors and has worked great. However, we now what it to not count column H. So I modified the formula to:

=IF('3RD SHIFT CHECKS'!B14="NIU","NIU",IF(COUNTIFS('3RD SHIFT CHECKS'!C14:G14,"",'3RD SHIFT CHECKS'!I14:O14,"")>0,"VERIFY","PASS"))

I now get a #VALUE! error in the cell. What can I do to correct this?

Answer 1

I amended the formula to meet your requirements,

Please,

For 1st Shift tab in cell R5, try the formula

=IF('3RD SHIFT CHECKS'!B5="NIU","NIU",IF(COUNTIFS('3RD SHIFT CHECKS'!C5:G5,"")+COUNTIFS('3RD SHIFT CHECKS'!I5:O5,"")>0,"VERIFY","PASS"))

and copy down

Similarly for the 2nd and 3rd Shifts tabs use the formulas:

=IF('1ST SHIFT CHECKS'!B5="NIU","NIU",IF(COUNTIFS('1ST SHIFT CHECKS'!C5:G5,"")+COUNTIFS('1ST SHIFT CHECKS'!I5:O5,"")>0,"VERIFY","PASS"))

=IF('2ND SHIFT CHECKS'!B5="NIU","NIU",IF(COUNTIFS('2ND SHIFT CHECKS'!C5:G5,"")+COUNTIFS('2ND SHIFT CHECKS'!I5:O5,"")>0,"VERIFY","PASS"))

I hope these formulas give a solution to your problem

Regards

Jeovany

Answer 2

That worked perfectly! Thank you for the support.

Answer 3

https://docs.google.com/spreadsheets/d/1w1OVbLyV0WGzz_4jaKfZ41-if-dhacgw/edit?usp=drivesdk&ouid=113933262099932923787&rtpof=true&sd=true [docs.google.com]

Just focusing on the 1st Shift and 3rd Shift Check worksheets, I put the broken formula in 1st Shift cell R13.

Answer 4

Hi there

Your scenario is too complex for us to give you the right formula without seeing your file.

Please,

Make a copy of the workbook,

Replace employees names with a list like Name 1, Name 2, Name 3, ...etc.

Upload it to Onedrive, Dropbox, Google drive...

Then post the link here for us to access the file and provide you with a solution to your problem.

Regards

Jeovany