1

I'm stumped with an Excel issue. I have tried variations of COUNTIFs and INDEX MATCH and can't get it to work.

I have a spreadsheet with labels for each row vertically in the first column. what I want to do is, if the first cell of a row equals a specific value/string, then I want to count all occurrences of a different value/string in that row.

I can't for the life of me figure it out.

Edit:

If I have a table like so

|-----------------|------------------|------------------|------------------|
|       Blue      |        Dave      |        Dave      |                  |
|-----------------|------------------|------------------|------------------|
|        Red      |                  |        Dave      |                  |
|-----------------|------------------|------------------|------------------|
|      Yellow     |        Dave      |        Dave      |       Andy       |
|-----------------|------------------|------------------|------------------|
|       Green     |                  |                  |       Andy       | 
|-----------------|------------------|------------------|------------------|

My question is, how can I can count all occurrences of "Dave" in the row where the first column equals "Yellow"?

Improve this question
7
  • Can you be more concrete and specify some sample values, and the desired outcome given these values? Also provide the exact formula you tried with and in which cell(s) the output should go. Commented Sep 28, 2016 at 9:44
  • By different, do you mean different than the value in the first cell? Do you want to count unique values? Or all different values? I.e. if the first row is "A", "B", "C", "B", "A", should the result be 2 ("B" and "C") or 3 ("B". "C", "B")? Commented Sep 28, 2016 at 9:51
  • @trincot - Edited to be a little clearer Commented Sep 28, 2016 at 9:55
  • @V.K. - They don't have to be unique. It's just that the cell values are likely to be different to the values in the first column. Commented Sep 28, 2016 at 9:57
  • 1
    And another question: do you want to have this formula copied in each row and then calculate this for each row 'Yellow' or do you want to have one single formula for the whole table, which means you also need to locate the row which contains 'Yellow'? And in the latter case, what if there are more 'Yellow's in the first column? Shall it count only with the first one? Commented Sep 28, 2016 at 10:07

1 Answer 1

Reset to default
1

If your other sheet (say Sheet2) has "Yellow" in A1, try:-

=COUNTIF(INDEX(Sheet1!$B$1:$D$4,MATCH(A1,Sheet1!$A$1:$A$4,0),0),"Dave")

in Sheet2!B1

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.