COUNTIF to determine the occurrence of "specific text" from another sheet.

Matt Foss

I've been able to find close approximations to what I'm looking for, but have one missing piece. I have a summary sheet ("summary") that pulls data from another source form evaluation sheet ("source"). Essentially, I have a workflow in place on the summary that when stakeholders are identified for a specific client an update request with a link to the source's form evaluation is sent.

What I would like one of the columns in the summary sheet to do is count the number of updates received for a specific client (the evaluation form is drop-down based so as the user fills out the evaluation they must choose the specific client being evaluated). While I have a good understanding of how to count for specific text, I want to be able to accomplish this with a repeatable formula that in layman terms does the following: Review the source column with the client name and as criteria search the client name indicated on the summary column and count how many times it reoccurs.

As an example, assume that there are three stakeholders identified on the source and the update request with link to the form evaluation is sent to them. Two of the stakeholders respond which is fed into the summary. As you can see, I would expect the formula in the summary to return a value of two in the "Number of Responses Received" column. As I mentioned I want it to use the summary client name column as the source of the "specific text" as I want to avoid having to manually update it for each separate client.

Matt Foss

Figured it out! I was looking at this from too complex of a standpoint. All it took was a simple COUNTIF.

For reference, I used: =COUNTIF([Source Sheet Range 1], [Client Name]@row

Matt Foss

As another way to simplify what I'm looking for:

I would like the formula to tell me how many times does the client name on the corresponding summary row occur as the client name in the source column? While in a sense this is a "specific text" search, the intention is to have the formula use the data as it exists in the client name column. While the source sheet will have multiple occurrences of the same client name, the summary sheet will only reference it once.

Matt Foss

Figured it out! I was looking at this from too complex of a standpoint. All it took was a simple COUNTIF.

For reference, I used: =COUNTIF([Source Sheet Range 1], [Client Name]@row

Genevieve P.

Hi Matt,

I'm glad you figured it out! Thanks for sharing your solution.

Just a quick note - is your range {Source Sheet Range 1} looking into a Multi-Select column? If so, you may want to add in a HAS function. The Countif will only count if the one name appears on its own in a cell, not if it appears with other names as well.

Try this:

=COUNTIF({Source Sheet Range 1}, HAS(@cell, [Client Name]@row))

Matt Foss

@Genevieve P , luckily it is a single select column. That said, your additional context for multi-select is helpful and I might have a use for it within the same sheet!