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:
However, this formula seems to require you to specify the columns. I tried to change it to
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!