COUNTIFS Error when using IFERROR

Hello,

I am trying to use the COUNTIFS formula in a sheet where I need the formula to count all cells with the value matching the Claim Status value, within the month and year stated in the next column, but the formula keeps breaking when i add in the Claim Status as a condition to look for.

Does anyone know how this can be fixed to include all of the below conditions:

  • The month of the "Target Close Out Date" matches the month in the current row.
  • The year of the "Target Close Out Date" matches the year in the current row.
  • The "Claim Status" matches the value in the current row

This is the formula that im trying to get to count:

=COUNTIFS({Claim Status}, IFERROR([Claim Status]@row, 0), {Target Close Out Date}, IFERROR(MONTH(@cell), 0) = Months@row, {Target Close Out Date} IFERROR(YEAR(@cell), 0) = Year@row)

Look forward to hearing a solution! :)

Tags:

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Hi

    I think the IFERROR on your Claim Status is going to cause a problem and you don't actually need one as a missing Claim Status should not cause an error in the way a missing date will. Try removing it. You also need a comma before the final IFERROR.

    =COUNTIFS({Claim Status},[Claim Status]@row, {Target Close Out Date}, IFERROR(MONTH(@cell), 0) = Months@row, {Target Close Out Date}, IFERROR(YEAR(@cell), 0) = Year@row)

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!