# How to count rows with two values in any of the many columns in Excel?

Say I have some Excel columns that look like this:

``````    A       B       C       D
1   Amy     Bob     Carol   David
2   Carol   David   David   Carol
3   Bob     Carol   Amy     Amy
4   David   Amy     David   Amy
``````

I need to count the number of rows where both Amy and Bob occur, no matter which column they are in. In this example, the number should be 2 (row 1 and row 3).

I already know COUNTIFS with which I can count the number of rows with 'Amy' in column A (or any particular column), and 'Bob' in column B (or any particular column). The formula would be:

``````=COUNTIFS(A1:A4,"Amy",B1:B4,"Bob")
``````

However, this formula seems to require you to specify the columns. I tried to change it to

``````=COUNTIFS(A1:B4,"Amy",A1:B4,"Bob")
``````

and it returned 0, which is not what I'm after. I need to count the number of rows where both Amy and Bob occur, regardless of the columns they are in. How can I achieve this? Thanks!

You need to use a helper column where you check whether actual row contains both names:

`=COUNTIF (A1:D1,"Amy")*COUNTIF (A1:D1,"Bob")>0`

Then just count cells with `TRUE`:

`=COUNTIF (E:E,TRUE())`

• Thanks. So if I also need to count rows with both Amy and Carol, I need another helper column? One helper column for each combination. Is that right? Apr 27 '16 at 6:04
• Yes, you're right. Apr 27 '16 at 8:09
• Thanks. Your method works except that I have to change the last COUNT to COUNTIF. Also if I have a large number of rows and columns this requires quite some effort to create all the helper columns. Is there a way where I can put a (possibly quite convoluted) formula into one cell and get the result? Apr 28 '16 at 5:42
• Good point. That should be COUNTIF too. Apr 28 '16 at 6:34

Just break it up

=COUNTIF(A1:D1,"Amy")+COUNTIF(A1:D1,"Bob")

Alternatively, you can do it with an array function

=SUM(COUNTIF(A1:D1,{"Amy","Bob"}))

Then you can do things like:

=IFERROR(CHOOSE(COUNTIF(A1:D1,"Amy")+COUNTIF(A1:D1,"Bob"),"One Match","Two Matches"),"No matches")

• This counts ALL occurrences of Amy and Bob, instead of the ones being in the same row. Apr 27 '16 at 4:39
• I have the ranges restricted to a single row. Apr 27 '16 at 16:11
• Shoot. I juuust missed the five minute edit window for my previous comment. Please disregard it. Thanks, I fixed the typos. I had the ranges set to columns instead of rows, and I used COUNT instead of COUNTIF for one of the formulas. I also added a practical example. Apr 27 '16 at 16:19
• Down voting over a typo is pretty harsh. Apr 27 '16 at 16:20
• "Count the number of rows where both name occurs", "in this example the number should be 2". It's not a typo, your answer is not an answer to this question. Apr 28 '16 at 3:53