2

I'm sorry but I don't know how to google this. I have a table that looks like this:

response|item|            categoryA| categoryB
----------------------------------------------
Yes     item1             item1       item2
No      item2             item3       item4
No      item3
yes     item4
No      item1 
Yes     item2

Each item belongs to one of two categories: A or B. The items are also listed in the 'item' column. Each item appears in the Item column multiple times, but only in the Category lookup columns once. Each instance of an item comes with a response, yes or no. I need a count of how often the category A and B items receive a 'Yes' response, respectively.

For instance: the table above would yield

Category A: 1 Category B: 2

Thank you,

1
  • Try countifs() and there are several examples of this type of question on this site or stack overflow.
    – Solar Mike
    Oct 8, 2017 at 21:13

3 Answers 3

1

Use a helper column.  Based on your illustration, I’ll assume that your data are in Columns A, B, E, and F.  I’ll use C as the helper column.  Enter

=IF(A3="yes", IF(ISERROR(MATCH(B3,E$3:E$8,0)), IF(ISERROR(MATCH(B3,F$3:F$8,0)),"X","B"), "A"), "No")

into C3.  (I am assuming that the data begin in Row 3, because Rows 1 and 2 are headers.)  Adjust the 3 and the 8 to reflect the actual bounds of the Category A and B lists in Columns E and F.  Drag this (C3) down as far as you have item/response data.

This says, for each “response” that is “yes”,

  • if the “item” is in Category A (Column E), evaluate to A,
  • if the “item” is in Category B (Column F), evaluate to B,
  • else, evaluate to X.

If the “response” is not “yes”, evaluate to No:

      

Now you can just do =COUNTIF(C3:C8, "A") and =COUNTIF(C3:C8, "B").  And, once you’re done debugging, you can change the "X" and "No" to a null string ("").

0

I rarely use Excel, but maybe this will help anyway.
First of read this article about multi-column duplicates (basically what you're trying to achieve)

If you follow the article, you should end up with a column describing what is a duplicate and what is not.
From there on you need to identify the different duplicates. this can be done by changing the "Here I am! I'm a duplicate" into e.g "Category A" and "Category B" for the respective item.
Now that you have narrowed it down to Category and response, all that there is left to do is to count the Yes and Nos (I'm sure this can be done in Excel, but as I said I don't use excel much anymore). If you need some further help, or just didn't understand a word I wrote then leave a comment and ill try my best.

0

could you not just use a pivot table, and do a count of 'response'?

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .