Count the occurence of a value across multiple worksheets

Greetings! I'm Tim, and I'm just an average Excel user who found his way here through Google. I did a little poking around and this looks like a great community. Maybe I can contribute on future visits.

I'm wracking my brains over this one but I can't seem to get anywhere. What is easy on a single worksheet becomes a conceptual nightmare when extended across multiple sheets........

I have an Excel 2007 workbook with ten worksheets, each is 5 columns by *about 50 rows. My column headings are the same on each sheet. All of the cells are formatted as Text.

Please Login or Register  to view this content.

I need to know how many times each value in column C appears across the entire workbook, and I need the results to be inversly sorted by the number of times that each value appears.

Please Login or Register  to view this content.

Can someone help me out of this mess?

This chart handles duplicate values, too with no arrays. Use chart on right to collect the raw sums, then the formulas in A and B to collate/rank the data.

This is the formula I used to CountIf across multiple sheets:

=SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3}&"!C1:C1000"),"="&H2))

Then some tricky ranking stuff to put them in order, regardless of duplicate count values.

Not at all. I just imagined that the values you count could duplicate, and it's tough getting the Rank / Results section to properly sort without some sort of unique ID to make it happen.

Here's a slight variation on the column A formula, a little less traumatic and it doesn't evaluate twice like the first one did.

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

Thank you VERY much. I'm looking forward to evaluating this over the weekend.

I've attached an improved set of sample data which is a better representation of my production workbook. Notice that some of the values in the Name column are duplicated on the same sheet....and sometimes on other sheets. Some values will occur only once in the entire workbook; others will occur much more often.

I'm looking to find out how many times each value appears across all of the sheets. Perhaps your example will give me what I need....we'll see

A little help here...

Sounds good.

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

Hi Todd... Welcome to the forum.

Be sure to read through the Forum Rules so you can use and follow them effectively.

For instance, you'll need to start a thread of your own, not add on to an ageold post. In your thread, feel free to provide a link to this thread if you feel it is relevant. In your thread attach your own workbook(s) to help demonstrate your need/goals, too. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook in your new thread.

Thanks.