Formula to Distinguish Between "F" or "S" within a Range??

  • Thread starter Thread starter Marcia
  • Start date Start date
M

Marcia

Hi! I need a formula that will look at the values in a column range
and return a "1" if the values are the letter "F," or return a "2" if
the values are the letter "S," or return nothing if there is no data
in the column.

Background: I use a Monthly Travel Report to record travel
information for residents living in a group home (the residents' names
are listed down the first column, and the dates of the month are
listed across the top row). I type either an "F" or "S" beside each
resident's name that went on the trip in the appropriate date's
column. The "F" or "S" indicates the shift the trip was taken (ie.,
"F" is for first shift, and "S" is for second shift). There will only
be ONE letter indicator used per column, NOT a mixture of both.

I use the =COUNTIF(C6:C32,"S")+COUNTIF(C6:C32,"F") formula in a row
below the names to count the number of residents that go on a trip for
any given day.

However, I must also calculate the total number of TRIPS (**NOT**
Residents) for each shift that are taken in the month. The only way I
know to do that is to put a formula in each date column across on the
bottom rows that will look to see whether there is an "F" or "S" in
the column above and, if so, return a "1" if it finds "F"s and a "2"
if it finds "S"s. Once I have that information, I can then use a
COUNTIF formula to count the 1s and 2s.

I tried using the following formula in each column:
=IF(COUNTIF(C6:C32,"F"),1,2)
which seemed to work fine at first, but is flawed because it returns a
"2" when there is NO DATA in the column. I need for the formula to
return NOTHING if there is no data in the column.

Can anyone help??

Thanks!

Marcia
 
So, for a single column there will only be a F or an S in the column. The
column won't have both an F and an S.
=IF(COUNTIF(C6:C32,"F"),1,IF(COUNTIF(C6:C32,"S"),2,"")

If you can have trips in both the first and second shifts on the same date,
you will need formulas in two rows.

or you can sum the above

=sign(COUNTIF(C6:C32,"S"))+sign(COUNTIF(C6:C32,"F"))*2

so if the cell shows

1 - first shift trip
2 - second shift trip
3 - both first and second shift trip

or if you want a count per day
=sign(COUNTIF(C6:C32,"S"))+sign(COUNTIF(C6:C32,"F"))

Regards,
Tom Ogilvy
 
You just need another embedded IF and COUNTIF formula to look for the S's:

=IF(COUNTIF(C6:C32,"F"),1,IF(COUNTIF(C6:C32,"S"),2,""))

There's probably an easier way to get your final counts, but I don't understand your layout well
enough to offer any suggestions at the moment.
 
Back
Top