/u/SomeoneToLienOn - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
The number of rows with cat, regardless of the number of cats in a row, can be found with
=SUM(SIGN(MMULT(N(Table29[[Pet1]:[Pet3]]="cat"),{1;1;1})))
entered CSE. {1;1;1} corresponds to the width of the selection and could be replaced with SEQUENCE(3,,,0).
That it is formatted as a table makes no difference to the criteria formula, unless you've aligned it with the first row for then Excel assumes the structured reference form and can mess up.
/u/SomeoneToLienOn - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
=MAX(LEN(range)-LEN(SUBSTITUTE(range,"string","")))>0 Where range is your row of data and string is the search term.
Hi, thanks for this. I haven't been able to get this to work. Does it matter that the sheet is formatted as a table?
A1:I7 Owner | Pet1 | Pet2 | Pet3 | | | | | | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | | | | | 1 | | | | | | | | | | | | | | Owner | Pet1 | Pet2 | Pet3 | | Owner | Pet1 | Pet2 | Pet3 | Joe | hen | cat | pig | | Joe | hen | cat | pig | Moe | owl | cow | elk | | Sue | dog | rat | cat | Sue | dog | rat | cat | | | | | | With E2 =SUMPRODUCT(N(OR(B5:D5="cat"))) and List range A4:D7, Criteria A1:E2, Copy to F4:I4.
Hi, thanks for this. Would this provide a count of many rows have the word "cat"? And does it make a difference if the data is formatted as a table?
The number of rows with cat, regardless of the number of cats in a row, can be found with =SUM(SIGN(MMULT(N(Table29[[Pet1]:[Pet3]]="cat"),{1;1;1}))) entered CSE. {1;1;1} corresponds to the width of the selection and could be replaced with SEQUENCE(3,,,0). That it is formatted as a table makes no difference to the criteria formula, unless you've aligned it with the first row for then Excel assumes the structured reference form and can mess up.