# Finding Percentage of Awarded Projects from a changing list of Projects

1. ## Finding Percentage of Awarded Projects from a changing list of Projects

Hi, this is my first post so bear with me please!

I am working on building an Excel Sheet that has a list of projects I have bid on. This list is being added to all the time. The most recent projects get added to the bottom of the list. I want to keep a running percentage of the number of projects awarded divided by the number of projects bid on:

# projects awarded / # projects bid

The tricky part is combing functions in the cell where the percentage is displayed (at the top of the page) because the number of projects will change as I add to the list. So I cant just divide by a constant number. I am looking for a function that will find the number of cells in a Column that have data in them, and divide by a different number.

What I was thinking was having a column (titled "Awarded ??") that will be populated with "yes" if the project was awarded, and left blank if the project was not given to me. Then I will use a COUNT function to find the number of "yes". Then I just need to find the total number of projects ( at that point in time) and divide the two.

I have tried using two count functions (counting names of projects, and counting job numbers if awarded) and dividing them but was given an error message of "#DIV/0!"

Any suggestions would be a huge help, Thanks!

2. ## Re: Finding Percentage of Awarded Projects from a changing list of Projects

I think you should investigate the COUNTIF and COUNTIFS functions. Also, possibly the COUNTA function.

3. ## Re: Finding Percentage of Awarded Projects from a changing list of Projects

Hello swvogt and Welcome to Excel Forum.
Since the new projects are added to the bottom and since you would like to have the formula update as new projects are added I would suggest using an Excel Table. When you are ready to add a new project go to the furthest cell to the right in the last row (B7 in the attached file) and press the tab key.
When a new project name is typed in the following formula will automatically update:
Formula:
`Please Login or Register  to view this content.`

Note: the counta function counts any cell containing text, however since you said that projects not awarded will be left blank it works, if you decide later to input some text in those cases where projects are not awarded, you will probably want to use countifs.
If the attached file doesn't work for you then please attach a sample workbook showing the type layout and expected result (manually calculated) you want.
To attach a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

4. ## Re: Finding Percentage of Awarded Projects from a changing list of Projects

Hi guys, thanks for the responses! I tried the CountA function using tables, and that worked well. I also solved it a different (more complicated) way using IF and OR functions. Attached is the sample file with Sample numbers. The Table function is used near the top labeled "# Bids Awarded / # Bids". The complicated functions are used in column Q and do essentially the same thing.

Thanks again for the help!

5. ## Re: Finding Percentage of Awarded Projects from a changing list of Projects

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

There are currently 1 users browsing this thread. (0 members and 1 guests)