Dropdown List with COUNTIFS

[Matt65]

Hi, I'm using Excel 2010.

So, I have 1 worksheet that is broken up horizontally by "activities," and there are 10 of these activities. Below the title of each activity is a dropdown list to select topics that were discussed during that activity. It look something like this:

Activity 1	Activity 2
Dropdown list	Dropdown list
Data	Data

<tbody>
</tbody>

On a second worksheet I have a list of each of the potential topics from the dropdown list and want to be able to count the number of activities in which each topic was discussed. It looks something like this:

Topic	#
Topic 1	1
Topic 2	3

<tbody>
</tbody>

I tried to do this with a SUM of COUNTIFS with this formula:

=SUM(COUNTIFS('Activity Log'!D$6:D$11,D6,'Activity Log'!D$13,">0"),COUNTIFS('Activity Log'!M$6:M$11,D6,'Activity Log'!D$13,">0"),etc...)

which actually worked on a previous version of the document. However, now when I do this it returns #VALUE! with the note ERROR IN VALUE. I've put the dropdown list in Text format, so is there some reason why I cannot do a COUNTIFS with text data from a dropdown list.

Thanks,

Matt

 

[Aladin Akyurek]

You need to explain your range and cell references your formula uses.

 

[Matt65]

Ok,

'Activity Log'!D$6:D$11: These are the 6 cells with dropdown lists in them for "Activity 1" from above. This allows the opportunity to select up to 6 topics that were covered in the activity. Each one is a text cell with topics like: "financial management" and "internal management" to choose from. 'Activity Log'!M$6:M$11 is the same dropdown list that allows for selecting from the same group of topics for "Activity 2."

D6: This is essentially the "Topic 1" cell in the second worksheet mentioned above. It corresponds with the first topic available in the dropdown lists. D7, D8, etc... each have one of the other topic choices.

'Activity Log'!D$13,">0": This is a cell recording the number of hours in which the activity took place, and should have a positive number for that activity to be counted in the second worksheet.

The formula is going in cell E7, which corresponds to the "1" cell in the second worksheet above.

Thanks for your patience, and I hope that helps.