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.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!