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.
Thanks!
Best Answers

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

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

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)

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.

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

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)

I think you'll need a NOT function:
=COUNTIFS({Estimate#_Perspective_2020}, NOT(CONTAINS("R", @cell)), {WeekNumber_Perspectives_2020}, WeekNumber@row)

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.

David,
Disregard my last message. It actually worked. Not sure what was going on with it the first time I tried it.
Help Article Resources
Categories
Check out the Formula Handbook template!