# Thread: Need help with COUNT/COUNTIF/ COUNTIFS

1. ## Need help with COUNT/COUNTIF/ COUNTIFS

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. Can you post a sample workbook showing the layout you have and what results you might expect?

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. 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.

5. Thank you so much for your help.