Results 1 to 5 of 5

Thread: Need help with COUNT/COUNTIF/ COUNTIFS

  1. #1

    Need help with COUNT/COUNTIF/ COUNTIFS



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

    Hi

    I need help finding the percentage of items within a collection. 3 to 10 books can make up a collection, but we might not have all the books needed to complete the collection. So I need to figure out the percentage of books within a collection. Example: One collection needs 10 books to be complete. We have 7 books, so the collection is 70% completed.

    We have 33,245 collections that contain anywhere from 3 to 10 and sometimes more books. Please HELP!!!


    (I've been using count and countif, but it is taking way too long to go one by one.)

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,462
    Articles
    0
    Excel Version
    Excel 2016
    Can you post a sample workbook showing the layout you have and what results you might expect?


  3. #3
    Collection # Books Needed Books Available Title
    9781600156625 9780736851572 9780736851572 Matter
    9781600156625 9780753462355 9780753462355 Materials
    9781600156625 9780778736486 9780778736486 Changing Materials
    9781600156625 9781432914097 9781432914097 Water
    9781600156632 9780822568162 #N/A What Is Solid?
    9781600156632 9780822568179 #N/A What Is Liquid?
    9781600156632 9780822568186 #N/A What Is Gas?
    9781600156649 9780822568162 #N/A What Is Solid?
    9781600156649 9780822568179 #N/A What Is Liquid?
    9781600156649 9780822568186 #N/A What Is Gas?
    9781600156656 9780516246635 9780516246635 Solids, Liquids, And Gases
    9781600156656 9780516246642 9780516246642 What Is Velocity?
    9781600156656 9780516246659 9780516246659 What Are Atoms?
    9781600156656 9780516246666 9780516246666 What Is Mass?
    9781600156656 9780516258454 9780516258454 What Is Electricity?
    9781600156656 9780516268644 9780516268644 Push And Pull
    9781600156663 9780516449142 9780516449142 How Do You Know It's Spring?
    9781600156663 9780516449159 9780516449159 How Do You Know It's Winter?
    9781600156663 9780516449227 9780516449227 How Do You Know It's Fall?
    9781600156663 9780516449234 9780516449234 How Do You Know It's Summer?
    9781600156670 9780516449142 9780516449142 How Do You Know It's Spring?
    9781600156670 9780516449159 9780516449159 How Do You Know It's Winter?
    9781600156670 9780516449227 9780516449227 How Do You Know It's Fall?
    9781600156670 9780516449234 9780516449234 How Do You Know It's Summer?
    9781600156687 9781404847552 #N/A The Autumn Leaf
    9781600156687 9781404847569 #N/A The Busy Spring
    9781600156687 9781404847576 #N/A The Summer Playground
    9781600156687 9781404847583 #N/A The Cold Winter Day
    9781600156694 9781404847552 #N/A The Autumn Leaf
    9781600156694 9781404847569 #N/A The Busy Spring
    9781600156694 9781404847576 #N/A The Summer Playground
    9781600156694 9781404847583 #N/A The Cold Winter Day
    9781600156700 9780516268316 9780516268316 Fall Leaves
    9781600156700 9780736817073 9780736817073 My Apple Tree
    9781600156700 9780823981021 9780823981021 Learning About Leaves
    9781600156700 9781560659495 9781560659495 Apple Trees
    9781600156700 9781560659518 9781560659518 From Bud To Blossom
    9781600156700 9781560659587 9781560659587 Autumn Leaves
    9781600156700 9781578743612 #N/A We Need Trees
    9781600156700 9781583408834 9781583408834 Birds Need Trees - Trees Need Birds
    9781600156717 9780516268316 9780516268316 Fall Leaves
    9781600156717 9780736817073 9780736817073 My Apple Tree
    9781600156717 9780823981021 9780823981021 Learning About Leaves
    9781600156717 9781560659495 9781560659495 Apple Trees
    9781600156717 9781560659518 9781560659518 From Bud To Blossom
    9781600156717 9781560659587 9781560659587 Autumn Leaves
    9781600156717 9781578743612 #N/A We Need Trees
    9781600156717 9781583408834 9781583408834 Birds Need Trees - Trees Need Birds
    9781600156724 9780061573972 #N/A Fletcher And The Falling Leaves
    9781600156724 9780064451208 #N/A Be A Friend To Trees
    9781600156724 9780064451963 9780064451963 A Tree Is A Plant
    9781600156724 9780142407431 #N/A Our Tree Named Steve
    9781600156724 9780316309035 #N/A Tell Me Tree
    9781600156724 9781580891691 #N/A Winter Tree
    9781600156731 9780618179992 9780618179992 Teedie
    9781600156731 9780679806288 #N/A Follow The Dream: The Story Of Christopher Columbus
    9781600156731 9780689862762 9780689862762 Stealing Home - Jackie Robinson: Against The Odds
    9781600156731 9781416971443 9781416971443 Barack Obama Son Of Promise, Child Of Hope
    9781600156731 9781423104087 9781423104087 Abe's Honest Words

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,462
    Articles
    0
    Excel Version
    Excel 2016
    If you can replace the #N/A with blanks, then you can use Pivot Table, and drag Collection # to Row Label, Books Avail and Books Needed to Summary section, then add a formula beside the pivot table dividing the Needed/Avail.
    Attached Files Attached Files


  5. #5
    Thank you so much for your help.

Tags for this Thread

Posting Permissions

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