Trying to count how many columns contain a phrase in any row where another phrase is present

Our take

To effectively count how many columns contain the word "present" in rows where "Coopers" is listed, you can utilize a combination of Excel functions. Start by filtering the relevant rows to target only those with "Coopers." Then, apply the `COUNTIFS` or `SUMPRODUCT` function to count the columns that meet both criteria. This approach allows you to track delegate attendance across meetings efficiently, providing clarity on how often "Coopers" had delegates present. Let’s explore the formula that can simplify this process for you.

I have a sheet recording attendance at meetings where there are delegates from different companies. I want to be able to count how many times a specific company has had a delegate present.

similar sheet using example data

In the pictured example, you can see that Googie's only delegate was present at 4 meetings, which would be easy to count:

=COUNTIF(C12:I12,"present")

Coopers had at least 1 delegate at 6 meetings.

I can get a total number of cells with "present" in them where column B has "Coopers" in it:

=SUMPRODUCT((B2:B14="Coopers") * ISNUMBER(SEARCH("present",(C2:I14)))

How can I get it to filter only rows with "Coopers" in them and count how many columns (not cells) contain the word "present"?

I'm using Microsoft Excel for Microsoft 365 MSO

submitted by /u/eggplantUK
[link] [comments]

Read on the original site

Open the publisher's page for the full experience

View original article →

Trying to count how many columns contain a phrase in any row where another phrase is present

Tagged with