Trouble with an IF formula

Hello!

I'm having trouble with an IF formula (combined with two COUNTIFS formulas as results) where I'm trying to count specific rows in a sheet based on certain criteria. The IF statement is to count status if the quarter is current or before, otherwise exclude status if the quarter is in the future. Both COUNTIFS formulas work just fine on their own, and the first COUNTIFS statement works with the IF statement, but when I add the second COUNTIFS statement, I get zero when I know there should be values.

The formula is below:

=IF(INDEX({Ref Quarter}, MATCH(MONTH(TODAY()), {Month#})) + "/" + YEAR(TODAY()) < [Chosen Quarter/Year]#, COUNTIFS({Platform/Category}, [Platform/Category]@row , {Status}, Status@row , {Finish Quarter}, [Chosen Quarter/Year]#), COUNTIFS({Platform/Category}, [Platform/Category]@row , {Status}, <>"Canceled", {Finish Quarter}, [Chosen Quarter/Year]#))

Can someone please help me figure this out?

Thanks!

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

Part of the issue is in the first part of your IF statement. You are creating a text string and trying to say "less than" a certain value. Are you able to provide some screenshots of those two pieces?

Shruti Pradhan

Hi Tim, the issue can be with the second COUNTIFS, the criteria needs to be in quotes ("< >Canceled"). Also, it works best if your chosen quarter/year is a date column.

Tim Dollmeyer

Thanks for the response!

I tried the IF statement on it's own, giving a 1 if true, and a 0 if false and it worked.

I also found that the month & year formulas are not working as well. The year formula is the simplest:

=IF(YEAR(TODAY()) < [Chosen Year]#, COUNTIFS({Platform/Category}, [Platform/Category]@row , {Status}, Status@row , {Finish Year}, [Chosen Year]#), COUNTIFS({Platform/Category}, [Platform/Category]@row , {Status}, <>"Canceled", {Finish Year}, [Chosen Year]#))

Here's a screenshot of the sheet.

Tim Dollmeyer

I think I figured it out. When I reversed the COUNTIFS statements, it gave me the correct values. I don't understand it, but it works!

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.