Google spreadsheet how to count data if contains a value-CodePudding

been trying to solve this but am struggling. Hopefully this is the right place to ask.

What I need to do is search a row for a certain word, say "cat". If that word is found within that row then take the value of another cell ("Gain") in that row and add this to a total. Then what I need to do is take that total, and divide it by the number of times "cat" was found within a group of rows. Is this possible?

Hopefully that explains what I am trying to do.

For example my data looks likething like this -

1       2     3     4         5         Gain
1/6/22  cat   bear  elephant  sheep     7
2/6/22  dog   cat   mouse     cow       12
3/6/33  cat   cow   horse     goat      5

Cow total: 2
Rows containing cow / gain (2/12 5) = 8.5

EDIT: What I have noticed it if I use SUMIF it will work ASLONG as the value I am search for is across a single column. However, if it is spreadout across multiple columns I get a value that isn't correct.

CodePudding user response：

try:

=COUNTIFS(B1:E3, A6)

and then:

=INDEX(SUM(IF(B1:E3=A6, F1:F3, ))/COUNTIFS(B1:E3, A6))

CodePudding user response：

This is the formula I use when I need to count how many cells have specific values in it within a range:

=COUNTif($J56:$J956,"=Cat")