0

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!

1

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? – Betty Apr 27 '16 at 6:04
  • Yes, you're right. – Máté Juhász 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? – Betty Apr 28 '16 at 5:42
  • Good point. That should be COUNTIF too. – Máté Juhász Apr 28 '16 at 6:34
0

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. – Máté Juhász Apr 27 '16 at 4:39
  • I have the ranges restricted to a single row. – picobit 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. – picobit Apr 27 '16 at 16:19
  • Down voting over a typo is pretty harsh. – picobit 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. – Máté Juhász Apr 28 '16 at 3:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.