Average if contains a specific word
Hi!
I'm trying to type a formula which could tell me the percentaje of the cells that are in color green, those cells are painted in green when I type the word "Listo", so I found that formula (showed in the image) in the community questions, but when I type that formula it drops me a 0. Could somebody tell me how can I make it work in my sheet?
Thank you!
Best Answers

The syntax of the formula is only slightly off, but your data isn't going to work as is. You need to average numbers, but your formula is trying to average text.
If you count the number of cells containing "Listo", then divide by the total number of cells, that should give you an average.
=COUNTIFS(CHILDREN(), "Listo") / COUNTIFS(CHILDREN(), OR(@cell = "", @cell<> ""))

If I understand correctly, then this should work...
=COUNTIFS(CHILDREN(), OR(@cell = "Listo", @cell = "No aplica")) / COUNTIFS(CHILDREN(), OR(@cell = "", @cell<> ""))
Answers

The syntax of the formula is only slightly off, but your data isn't going to work as is. You need to average numbers, but your formula is trying to average text.
If you count the number of cells containing "Listo", then divide by the total number of cells, that should give you an average.
=COUNTIFS(CHILDREN(), "Listo") / COUNTIFS(CHILDREN(), OR(@cell = "", @cell<> ""))

Hi Paul!
Thank you, it worked! :D

Happy to help! 👍️

Hi Paul!,
Me again, do you know how can I complement this formula if I want to include " No aplica" so that to don't count this sentence and to only sum the ones which are in color green. So even if I have all those CHILDREN painted in green but I have that " No aplica" sum anyways a 100%.
Thank you!

If I understand correctly, then this should work...
=COUNTIFS(CHILDREN(), OR(@cell = "Listo", @cell = "No aplica")) / COUNTIFS(CHILDREN(), OR(@cell = "", @cell<> ""))

Thank you Paul, it worked perfectly!

Great! Happy to help! 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!