COUNTIFS + FIND Question

Hi I am trying to figure out how to count the number of times during the week that there is a revision to a project. The revisions are figured out by looking at the Estimate #. If the Estimate # has an "R" in them then I need the formula to count those. The current formula that I have is:

=COUNTIFS({Estimate#_Perspective_2020}, FIND("R", {Estimate#_Perspective_2020}, >0), {WeekNumber_Perspectives_2020}, WeekNumber@row)

This gives me an #INVALID DATA TYPE error.

See the screenshot below to see if you might be able to help me out.

Screen Shot 2020-10-22 at 10.49.28 AM.png

Thanks!

Find more posts tagged with

Accepted answers

David Tutwiler

Try this one:

=COUNTIFS({Estimate#_Perspective_2020}, CONTAINS("R", @cell), {WeekNumber_Perspectives_2020}, WeekNumber@row)

Matt Scroggins

David,

Disregard my last message. It actually worked. Not sure what was going on with it the first time I tried it.

All comments

David Tutwiler

I think you are passing the FIND too many parameters. Try this:

=COUNTIFS({Estimate#_Perspective_2020}, FIND("R", {Estimate#_Perspective_2020}) >0, {WeekNumber_Perspectives_2020}, WeekNumber@row)

Matt Scroggins

For some reason that helped me figure out the number of new projects instead of the number of revisions. That was the next thing that I was going to figure out but I am still not able to get the correct count for revisions.

David Tutwiler

Try this one:

=COUNTIFS({Estimate#_Perspective_2020}, CONTAINS("R", @cell), {WeekNumber_Perspectives_2020}, WeekNumber@row)

Matt Scroggins

That worked!!! Can I asked you one more question? If I wanted to get the count of new projects what would I need to do? I have tried to do the below formula which sounds right to me but for some reason something is not working correctly.

=COUNTIFS({Estimate#_Perspective_2020}, CONTAINS("<>R", @cell), {WeekNumber_Perspectives_2020}, WeekNumber@row)

David Tutwiler

I think you'll need a NOT function:

=COUNTIFS({Estimate#_Perspective_2020}, NOT(CONTAINS("R", @cell)), {WeekNumber_Perspectives_2020}, WeekNumber@row)

Matt Scroggins

Hi David,

Sorry it took so long to respond. I got moved to a different project and I am just now getting back to this. I tried adding the NOT function before CONTAINS but it gives me a #UNPARSEABLE error.

Matt Scroggins

David,

Disregard my last message. It actually worked. Not sure what was going on with it the first time I tried it.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.