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
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!