Using SUMPRODUCT & COUNT Functions

**Weasel** · 06-06-2008, 02:11 PM

My spreadsheet has two separate sheets. One is a data list and the other is a report sheet. Here's how my data sheet looks:

Please Login or Register  to view this content.

As you can see the column A represents a group number and column B represents a persons name that is in that group. In my report sheet I'm trying to create a formula that will count every name for that is in each group. Here's how my report sheet would look:

Please Login or Register  to view this content.

Column A again represents the group number while column B now just shows that total number in each group. I'm guessing I need to use a combination of a sumproduct and count function but I'm having trouble putting it together. Any help would be greatly appreciated.

**NBVC** · 06-06-2008, 02:20 PM

Assuming the names are unique for each group... then you can just count the number of times a group number appears...

e.g =Countif(Sheet1!A:A,A1)

where Sheet1!A:A contains your original listing of groups and A1 is the number 1 in your report representing Group 1.

Then formula can then be copied down.