# Thread: Excel spreadsheet, formula be for counting families on a sheet of individuals?

1. ## Excel spreadsheet, formula be for counting families on a sheet of individuals?

Hi Folks,
I have attached a file called "Spreadsheet Query". It is similar to a much larger file but the contents will suffice for my question. What should the formula for "K2" be? The formula for L2 is (=COUNTIF(\$G\$2:\$G\$750,J2)". You will see that Column "L" counts the number of individuals assigned to, in this case James Bloggs. I want to find out how many families he is responsible for, column "B" has the information for this, the number of times each number occurs. You will see eg Arnold is a family of 4, parents and two children, Balmer is a family of three, Archer is a family of one. I have tried several permutations of Countif and others but to no avail.
Many thanks in anticipation.

Lismurn

2. ## Excel spreadsheet, formula be for counting families on a sheet of individuals?

Hi Folks
Apologies, it seems that my attachment did not in fact attach, I think it has now done so. Please read my first post to find out my problem.

Lismurn

3. Originally Posted by Lismurn
You will see eg Arnold is a family of 4, parents and two children, Balmer is a family of three, Archer is a family of one.
Where?
Can you write the final results in the K column. What these columns take into consideration for the calculation of the K column.
Be creative

4. Sorry, it should be the "I" column, I had removed unnecessary columns obviously after I had written my message. James Bloggs should show 1 family, Robert Bloggs should be 2 families and Joe McDowell 3 families etc.

5. ## Count Unique from two columns based on criteria

Try this formula in to I2 cell
Code:
`=SUMPRODUCT(((\$F\$2:\$F\$24=H2))/COUNTIFS(\$F\$2:\$F\$24;\$F\$2:\$F\$24&"";\$B\$2:\$B\$24;\$B\$2:\$B\$24&""))`
see attach

6. Hi navic,
Brilliantly successful, many thanks, also thanks for the links. I want to work through your formula to see what each part does.

Thanks again
Lismurn

7. Originally Posted by Lismurn
I want to work through your formula to see what each part does.
You can see Evaluate Formula

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•