Interesting Excel Function

T

tester

New Member
I have a list of 4 columns and 169 rows to record what people have picked in a competition. I want to see how many people have made the very same selections (each person makes 4 selections). Is there a function I can use for this? Look at the attachment. Is there a function to count how much times "blue, gold, silver, purple" are picked in the same row?View attachment New Microsoft Excel Worksheet.zip
 
You could use the countif() function such as =countif(A1:A169,"blue") which would tell you how many chose blue in col A. Or, =countif(A1 : D169,"blue") to count the total number of times blue was chosen in all 4 columns. (Remove the spaces between A1 : D169)
 
Last edited:
Hello tester. I've had plenty of experience with Excel so I am here to help. I was considering the "COUNTIF" function. Such as the formula =COUNTIF(A1:A4,"blue") would return back "3" since there are three "blue" in the first column.
 
NDSUTopGun said:
Hello tester. I've had plenty of experience with Excel so I am here to help. I was considering the "COUNTIF" function. Such as the formula =COUNTIF(A1:A4,"blue") would return back "3" since there are three "blue" in the first column.
Click to expand...

BAM! instant help lol :P
 
A selection is a row of 4 colours each so "blue gold silver purple" is just one selection. I want to count how many times this selection is made (which is twice in the example but 70 or 80 in the real list), and not just the amount of times the single word "blue" was picked.
 
So the criteria is how many times blue, gold, silver, purple got selected. There's more than those 4 colors though, right? I don't think there is a function that could do it for you but you could write a vba macro that would loop through and count the number of times each set of colors is encountered.
 
Last edited:
Here is your spreadsheet where I created a vba macro to count how many times a particular set of colors occurred. Run the macro by pressing Ctrl-s. To clear the results before re-running, pres Ctrl-c. You can view the macro by pressing Alt-F11.
 
Last edited:
You must log in or register to reply here.
Back
Top