COUNTIFS + FIND Question

Matt Scroggins

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.

Thanks!

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.

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.