Results 1 to 7 of 7

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #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
    Attached Files Attached Files

  3. #3
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by Lismurn View Post
    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
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  4. #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. #5
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013

    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
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  6. #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. #7
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by Lismurn View Post
    I want to work through your formula to see what each part does.
    You can see Evaluate Formula
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Posting Permissions

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