[handymom77]

Hello,

I have racked my brain and cannot come up with the answer to this situation. Granted, everything I learned about spreadsheets I learned in high school and on my own :rolleyes:

We are sponsoring a free car and bike show next month. I want to create a spreadsheet or database that can accommodate the ballot counting. Without going into great detail, is there a formula that can figure how many X there are, not the sum of them? X is the entry number of the vehicle. So the spreadsheet would have the Car Classes across the top and the ballot numbers down the side. I can elaborate more if someone thinks I'm making any sense:) If there is no formula, I can just sort and manually count the fields... but that would be very time consuming.

[JBeaucaire]

There's absolutely columnar counting formulas. There's SUMIF() and COUNTIF()... and some fancy implementations of SUMPRODUCT().

Post up a sample copy of the ballot sheet filled out, include examples of what the "count" should be and how you'd like to see it displayed as well. I'm sure we can offer something quickly.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

[handymom77]

So, I would want a formula that would tell me first, second, and third places. The numbers down the left side are ballot numbers and the titles across the top are car classes (there are 20 total). Please let me know if you need any other information. I am excited that this might work!

[JBeaucaire]

It will definitely work once it's clear what you're after. It's still not.

Your list of 31 ballots:
1) only has values in 2 columns, are almost ALL duplicate values
2) was supposed to include sample results so I can see what you consider the answer to be based on the sample data

Please shorten up the example data if it's too long for you to mock up a complete example set including sample results. Explain the results if it's not clear on the sheet why you indicate one winner over another.

Your example shows a lot of duplicates. What are the numbers in the data? Are those scores or are those "IDs" for someone?

[JBeaucaire]

I think I may have figured your sheet out.

1) You need a "totalling table" which I've created off to the right from your balloting table.

2) You need the table to be as long as you have IDs for that being voted for in each column. I included every number from 1-30, but you would only need the IDs that actually exist.

3) The table has as many "columns" as your input table

I've created the table for you. The one formula in Y2 is then copied down and across as needed:

=COUNTIF(B$1:B$31,$X2)

4) The formula in the totals below the balloting table are then this... in B34 and then copied down and to the right:

=IF(LARGE(Y$2:Y$31,LEFT($A34,1)+0)=0,"",IF(COUNTIF (Y$2:Y$31,LARGE(Y$2:Y$31,LEFT($A34,1)))>1,"Tie",IN DEX($X$2:$X$31, MATCH(LARGE(Y$2:Y$31,LEFT($A34,1)+0), Y$2:Y$31, 0))))

You don't indicate anything regarding TIE detection, so think about that and come back. For now, I've put a conditional formatting check into the "totalling table" that will highlight any TIES in any one column. The CF formula I used is:

=AND(Y2>0,COUNTIF(Y$2:Y$30,Y2)>1)

I manually typed "2" into two cells so you could see how it would light up to get your attention.

EDIT:
Sheet removed... see below for the latest version.

[handymom77]

JBeaucaire,

I'm sorry to have taken so long to reply. Busy day...

Amazing; that's the only word I can use to describe what you did. I'm sorry for the confusion in my information, but you got it right. Column A are ballot numbers which will mainly be used to keep track of how many ballots we count. Each car is given a numeric value, the entry number. This is a non-profit event so we are not sure how many participants and spectators will show up. Spectators will determine the winners by spectator voting ballots. We are not sure how many ballots and entry numbers there will be. That being said, I edited the spreadsheet to accommodate 500 of each. The chances are extremely low that we will 'run out of space'.

I experimented with different scenarios and wondered the following:
1. Can we move the totaling boxes to the top? I inserted three rows and adjusted the freeze pane. This will help with scrolling down through 500 rows.
2. I noticed, while trying to see the 'TIE' scenario you created, that some of the columns do not highlight when there is a tie. The highlighting function is very helpful.
3. I am considering numbering the ballots that we hand out in order to prevent duplications. In that case, it would be helpful to have another column (probably column B) that did not allow duplicate numbers. What are your thoughts?

I sincerely thank you again for your time.

[JBeaucaire]

1) Ranking Boxes On Top

You moved the boxes up and not the formulas? Why was that? I've moved up the formulas.

I also added the same "light up" function to the scoring table, anytime the value is "Tie" it will turn yellow.

2) Ties not all lighting up in totaling table

You expanded the table and expanded the formulas for scoring, but you didn't expand the conditional formatting formulas. Do you know how to find them?

Highlight the entire table of values that will use the same CF logic, then click on Format > Conditional formatting. All CF formulas are TRUE/FALSE formulas, so may look a little weird. Read the formula in there now and you'll see... when it evaluates to TRUE, the coloring is applied, when FALSE it is not.

3) Add Unique Ballot # column

Added. I also put in a CF formula so any duplicate ballot numbers will flag in red.

==========
You might consider moving the BALLOTING to one sheet, the SCORING to another, and the RESULTS to a third so you can format the results to look any way you want.

[handymom77]

JBeaucaire,

Thanks again for all your expertise.

The finished product is too big to upload here. I found out that the first year we had this car show there were over 700 votes so, I added 500 more rows to the sheet. I took your advice and separated everything out into three worksheets. I added protection to the formulas.

I couldn't have done this without you!