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! :)

Find more posts tagged with

Formulas

Accepted answers

KPH

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)

All comments

KPH

=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)

AravindGP

Hi @whitney.thomas

How do you have your date captured in the Target Close out date? Do you have Month and Year columns created in your source sheet based on your Target Close Out Date?

whitney.thomas

Thank @KPH!

That worked!

KPH

Wonderful! Thanks for letting me know.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.