Need formula and not sure where to start! Matching criteria.

[Greg]

Hello, I have been searching for two days on the internet and haven't found
anyone attempting this so far.

I would like to find a formula for this:

Cell's E1 through E6, if they match said criteria (ie...specific letters)
than replace those cells info with a "1" if not than replace cells with a
"0".

Thus I can sum my rows with 1's and 0's. Is this possible?

EXAMPLE:
E1
SF
if E1 matches "SF" then replace "SF" with a number value of "1". When I
attempt this, I get a Circle Error.

Thanks in advance.

 

[akphidelt]

Theres multiple ways of doing this but the best would be not to change the
info at all. If you want to do counts on strings then you can use countif.

So you can do =Countif($e$1:$e$6,"SF")

 

[Greg]

That would work if I was counting the columns, but in fact I am attempting to
sum rows.

Let me see if I can explain this better.

I have a huge football picks pool weekly. Each row is a CSV file of each
user with each column being an NFL team initials that they chose. I need to
tally up the right picks in each row. Thus I wanted to have each winning
pick....ie...the winning initials to be a "1" and then at the end of each
users row of picks, you would generate the total winning picks. Now with
several users, I have a long column of either or type data. So I wanted to
take that entire column for GAME 1 lets say and create a value for a win or
loss.

Does this make sense? Or is this too complicated?

 

[Greg]

I have found a time consuming and non-dynamic way of doing this;

Find and Replace.....SF with 1. This is assuming that the San Francisco
49ers won the game right, now I have to find and replace all winning team
initials for that week. Then at the end of each users row. I have a sum of
the entire row of 1's. Equaling a total of winning picks for that user. I
was hoping for a formula that would do that dynamically, so I could just
change the winning team for that game and that week.

 

[T. Valko]

Try this:

A2 = user name
B2:Q2 = weekly picks

List the weekly winners in a range of cells. They can be listed either
vertically or horizontally. Let's say the winners are listed in the range
X2:X17

To get the users correct number of picks enter this formula in say, R2:

=SUMPRODUCT(COUNTIF(B2:Q2,X$2:X$17))

Then you can copy down for other users.

I used to run a pool. Man was it a PITA! Doing it in Excel was the easy
part. Trying to get everyone to make their picks in a "timely manner" was a
freakin task!

 

[Greg]

Great, thank you...I will give this a try.

I have a really nice cformsII on my blog that allows for radio button data
forms that push to data tables and I download those as CSV files. It emails
everyone there picks and me as well. This all works so automated but this
was the clincher. Thanks much!

And yes, getting those picks in is the hard part. But with cash at
risk....they do pretty good!