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
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!