Counting occurrences over multiple columns and worksheets

[musoguy]

Hi folks.

I am trying to do a count of the number of times a text string is used over multiple columns and worksheets.

Allow me to explain:

On "sheet1" Cells C8:AH8 and C9:AH9 have people's names in them. Not every cell is populated, but most are, and there are multiple occurrences of each name.

"sheet 2" is an exact duplicate of "sheet1" but is for a different day of the week, but has the same setup.

What I am trying to do (in vain currently) is have a list on "sheet 3" Column A of every person's name that occur in the first two sheets. The part I am stuck with is in Column B I want a list of how many times each of these people have been listed in the first two sheets, as I cannot list them more than five times.

I hope this makes sense. It seems like there would be an easy explanation for this, but I just can't for the life of me figure it out!

Hoping someone else can

 

[mikerickson]

A formula like
=COUNTIF(SHEET1!C8:AH9, "Bob Smith") + COUNTIF(SHEET2!C8:AH9, "Bob Smith")

will tell you how many times Bob Smith is listed in those ranges.

 

[musoguy]

Mike, thank you very much for taking the time to help me out. That's just what I need

 

[musoguy]

Also, for anyone else using this thread to answer the same question for them, you can substitute the name for the value in a cell. So in my case I modified the formula slightly to read:

=COUNTIF(SHEET1!$C$8:$AH$9,A1) + COUNTIF(SHEET2!$C$8:$AH$9,A1)

That way it took the name from the same row in Column A and used that to perform the count. I also changed the cells to be counted to an absolute reference. That way I could just copy and paste the formula down the column, rather than having to manually update every cell's formula.

Hope this is of help to someone else