Average if contains a specific word

Sylvia Elizabeth

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!

Paul Newcome

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<> ""))

Paul Newcome

If I understand correctly, then this should work...

=COUNTIFS(CHILDREN(), OR(@cell = "Listo", @cell = "No aplica")) / COUNTIFS(CHILDREN(), OR(@cell = "", @cell<> ""))

Paul Newcome

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<> ""))

Sylvia Elizabeth

Hi Paul!

Thank you, it worked! :D

Paul Newcome

Happy to help! 👍️



Sylvia Elizabeth

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!

Paul Newcome

If I understand correctly, then this should work...

=COUNTIFS(CHILDREN(), OR(@cell = "Listo", @cell = "No aplica")) / COUNTIFS(CHILDREN(), OR(@cell = "", @cell<> ""))

Sylvia Elizabeth

Thank you Paul, it worked perfectly!

Paul Newcome

Great! Happy to help! 👍️