How to create a formula where a letter equals a number, i.e. Y = 1

Hi
I am creating a spreadsheet that we are using to record the number of
people that will be attending a meeting. Is there a formula that I can
enter so that a "Y" or "Yes" response will equal to 1 and I can
therefore tally up all of the positive responses?

Any help would be appreciated!

Thanks
Kathy

Hi

One way
=COUNTIF(A1:A100,"=y")

Regards

Roger Govier



Kathy wrote:

>Hi
>I am creating a spreadsheet that we are using to record the number of
>people that will be attending a meeting. Is there a formula that I can
>enter so that a "Y" or "Yes" response will equal to 1 and I can
>therefore tally up all of the positive responses?
>
>Any help would be appreciated!
>
>Thanks
>Kathy
>
>
>

Thanks Roger. This is good! How about this similar one? I need to
know the total number of Clients and Employees that are coming to each
event. In column I it is listed whether an individual is a client or
an employee. In column J it lists whether they are coming to the event
(a Y for Yes, N for N). I want to create a formula along the lines of:

SUM OF If client in column I and Y in column J then = 1.
SUM OF If employee in column I and Y in column J then = 1

Any ideas?

Hi Kathy

One way
=SUMPRODUCT(--($I$2:$I$100="C"),--($J$2:$J$100="Y"))
to give total for Clients.
Change "C" to "E" for Employee

Change ranges to suit, but ensure that they are of equal size.


Regards

Roger Govier



Kathy wrote:

>Thanks Roger. This is good! How about this similar one? I need to
>know the total number of Clients and Employees that are coming to each
>event. In column I it is listed whether an individual is a client or
>an employee. In column J it lists whether they are coming to the event
>(a Y for Yes, N for N). I want to create a formula along the lines of:
>
>SUM OF If client in column I and Y in column J then = 1.
>SUM OF If employee in column I and Y in column J then = 1
>
>Any ideas?
>
>
>

Hi Roger,
Thanks once again for the response. This formula doesn't seem to be
working for me. I checked out the Help section of excel and maybe it's
because SUMPRODUCT treats array entries that are not numeric as if they
were zeros. Is there a way to get it to recognize the words? Also,
what do the $ means?
Thanks
Kathy

I'm sure that Roger assumed you would realize that when he used "C" in the
formula, you would either match the contents of Column I to the "C" ... OR
.... you would revise the parameters in the formula to match the actual
contents of Column I.(Client(s) ... Employee(s)).

In other words, whatever you have in Column I, should be in the formula, or
vice-versa.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Kathy" <[email protected]> wrote in message
news:[email protected]...
> Hi Roger,
> Thanks once again for the response. This formula doesn't seem to be
> working for me. I checked out the Help section of excel and maybe it's
> because SUMPRODUCT treats array entries that are not numeric as if they
> were zeros. Is there a way to get it to recognize the words? Also,
> what do the $ means?
> Thanks
> Kathy
>

Hi Kathy

In what way does it not work?
What result do you get?
I had to guess at what your markers were for Customers or Employees in
column J. I chose "C" for Customer and "E" for Employee.
What have you used? Just change them accordingly.

The $ signs just lock the ranges as being absolute, so they won't alter
if you copy the formula down the page, it will still only look at rows
2:100 of column J and column I.
They do not affect whether the formula works or not.

All it is doing is testing each cell in the range to see if it contains
the relevant marker. It returns True or False. These values are co-erced
to 1 or 0 by the double unary minus -- in fron of each formula.

Sumproduct then multiplies these results together e.g.
0 * 0 = 0
0 * 1 = 0
1 * 0 = 1
1 * 1 = 1
and so on for the complete range

then it sums the series to give the answer, which will be the total
where both conditions are true.

Regards

Roger Govier



Kathy wrote:

>Hi Roger,
>Thanks once again for the response. This formula doesn't seem to be
>working for me. I checked out the Help section of excel and maybe it's
>because SUMPRODUCT treats array entries that are not numeric as if they
>were zeros. Is there a way to get it to recognize the words? Also,
>what do the $ means?
>Thanks
>Kathy
>
>
>

I am still confused. What would the formula be for the following?
Let's same employees is column a, column b is Event 1, Column c is
Event 2, Column d is Event 3. In this scenario how would I do totals
that reflect the total number of clients and the total number of
employees attending the events without sorting and seperating the two
types of attendees?

Employee y y y
Employee
Employee y y
Employee y y y
Client y y
Client y y
Client y y N
Client y y y

So, you now have 3 events ... in 3 separate columns?

This often occurs, where OPs start out with an over simplified explanation
of a situation, and then try to expand a suggested solution to their problem
to cover their entire, *actual* scenario.

Is attendance at all 3 events mandatory to count as *one*,
OR
Is attendance at each event to count individually as a 1, or a 2, or a 3?

In which case, you would want the totals for your last scenario to be:

Employee - 2
Client - 1
OR
Employee - 8
Client - 9

BTW - what happened to Column J?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Kathy" <[email protected]> wrote in message
news:[email protected]...
> I am still confused. What would the formula be for the following?
> Let's same employees is column a, column b is Event 1, Column c is
> Event 2, Column d is Event 3. In this scenario how would I do totals
> that reflect the total number of clients and the total number of
> employees attending the events without sorting and seperating the two
> types of attendees?
>
> Employee y y y
> Employee
> Employee y y
> Employee y y y
> Client y y
> Client y y
> Client y y N
> Client y y y
>

Once again, thank you so much for all of your help. The formula is now
working, but the totals are off by 1 or 2 total. For example, the
total for one figure should be 28, but by using the formula it is
coming up as 26. Listed below are columns I - L.

In column J, the following formulas are being used:
=SUMPRODUCT(--($I$2:$I$134="Employee"),--($J$2:$J$134="Y"))
=SUMPRODUCT(--($I$2:$I$134="Client"),--($J$2:$J$134="Y"))

In column K, the following formulas are being used:
=SUMPRODUCT(--($I$2:$I$134="Employee"),--($K$2:$K$134="Y"))
=SUMPRODUCT(--($I$2:$I$134="Client"),--($K$2:$K$134="Y"))

In column L, the following formulas are being used:
=SUMPRODUCT(--($I$2:$I$134="Employee"),--(L2:L134="Y"))
=SUMPRODUCT(--($I$2:$I$134="Client"),--(L2:L134="Y"))

Here are columns I-L of the spreadsheet, with totals on line 138. Any
ideas?

Client/Employee Cocktail Rec Cruise Fri Inv Sem
Client
Client
Employee
Client
Client y y y
Client y y y
Employee
Client y y
Client
Employee
Employee y y y
Client y y
Employee
Employee
Employee y y y
Client
Client y y y
Employee
Employee
Employee
Client y y y
Employee
Client
Client
Employee y y y
Client
Client y y
Client
Client
Employee
Employee y y
Client y y y
Employee y y y
Employee
Client
Client
Client y y y
Client y
Client y y y
Client
Employee
Employee
Client y y y
Employee
Client y y
Employee
Client
Client y y
Employee
Client
Employee
Client
Employee
Employee y y
Employee
Employee
Employee
Client
Employee
Employee
Client y y y
Client
Employee
Employee
Employee
Client y y
Employee y y y
Client
Client y y y
Client
Client
Employee y y y
Employee y y y
Client y y
Client y y
Employee
Employee
Employee y y y
Client y y y
Employee
Employee
Employee
Employee y y
Employee
Client y y
Employee
Employee
Employee
Employee y y y
Client y y y
Client
Client y y y
Client y y y
Client y y
Client
Employee
Client y y y
Client
Employee
Employee
Employee
Client
Employee
Client y y y
Client y y y
Employee
Employee
Employee y y y
Client
Client
Employee
Client
Client y y
Client y y
Client y y
Employee
Employee
Employee
Employee
Employee
Client y y
Employee
Client y y












10 13 13
16 30 31

Hi Kathy

Sorry not to have responded earlier, but I was away from my computer all
day yesterday.
I copied your data into a sheet, and applied the formulae and got the
following results

Cocktail Rec Cruise
Employee 0 10 13
Client 18 32 32

These values are different from those you show at the bottom of your
email quite considerably.
Maybe the data you posted, isn't quite the same as that in your sheet.
Perhaps you have a space before the word client or employee somewhere on
your sheet, or a trailing space after the name.

If you want to mail the original sheet to me directly, I would be happy
to take a look and see if I can see the problem.

Remove nospam from my email address to mail direct.

Otherwise, post back with any other observations.

Regards

Roger Govier



Kathy wrote:

>Once again, thank you so much for all of your help. The formula is now
>working, but the totals are off by 1 or 2 total. For example, the
>total for one figure should be 28, but by using the formula it is
>coming up as 26. Listed below are columns I - L.
>
>In column J, the following formulas are being used:
>=SUMPRODUCT(--($I$2:$I$134="Employee"),--($J$2:$J$134="Y"))
>=SUMPRODUCT(--($I$2:$I$134="Client"),--($J$2:$J$134="Y"))
>
>In column K, the following formulas are being used:
>=SUMPRODUCT(--($I$2:$I$134="Employee"),--($K$2:$K$134="Y"))
>=SUMPRODUCT(--($I$2:$I$134="Client"),--($K$2:$K$134="Y"))
>
>In column L, the following formulas are being used:
>=SUMPRODUCT(--($I$2:$I$134="Employee"),--(L2:L134="Y"))
>=SUMPRODUCT(--($I$2:$I$134="Client"),--(L2:L134="Y"))
>
>Here are columns I-L of the spreadsheet, with totals on line 138. Any
>ideas?
>
>Client/Employee Cocktail Rec Cruise Fri Inv Sem
>Client
>Client
>Employee
>Client
>Client y y y
>Client y y y
>Employee
>Client y y
>Client
>Employee
>Employee y y y
>Client y y
>Employee
>Employee
>Employee y y y
>Client
>Client y y y
>Employee
>Employee
>Employee
>Client y y y
>Employee
>Client
>Client
>Employee y y y
>Client
>Client y y
>Client
>Client
>Employee
>Employee y y
>Client y y y
>Employee y y y
>Employee
>Client
>Client
>Client y y y
>Client y
>Client y y y
>Client
>Employee
>Employee
>Client y y y
>Employee
>Client y y
>Employee
>Client
>Client y y
>Employee
>Client
>Employee
>Client
>Employee
>Employee y y
>Employee
>Employee
>Employee
>Client
>Employee
>Employee
>Client y y y
>Client
>Employee
>Employee
>Employee
>Client y y
>Employee y y y
>Client
>Client y y y
>Client
>Client
>Employee y y y
>Employee y y y
>Client y y
>Client y y
>Employee
>Employee
>Employee y y y
>Client y y y
>Employee
>Employee
>Employee
>Employee y y
>Employee
>Client y y
>Employee
>Employee
>Employee
>Employee y y y
>Client y y y
>Client
>Client y y y
>Client y y y
>Client y y
>Client
>Employee
>Client y y y
>Client
>Employee
>Employee
>Employee
>Client
>Employee
>Client y y y
>Client y y y
>Employee
>Employee
>Employee y y y
>Client
>Client
>Employee
>Client
>Client y y
>Client y y
>Client y y
>Employee
>Employee
>Employee
>Employee
>Employee
>Client y y
>Employee
>Client y y
>
>
>
>
>
>
>
>
>
>
>
>
> 10 13 13
> 16 30 31
>
>
>

Hi Roger!
Thank you so much. I think I figured it out. Like you suggested it
appeared to be a formatting error in that perhaps some of the y's were
uppercase or had a space in front of it. I went back through and made
sure everything was uniform and now it appears that the formula is
working!
Thanks again and have a great day!!
Kathy

Hi Kathy

Thanks for the feedback, glad to hear it has worked out for you.
The fact that it was "Y" or "y" wouldn't matter with this formula, but
leading or trailing spaces would.

Regards

Roger Govier



Kathy wrote:

>Hi Roger!
>Thank you so much. I think I figured it out. Like you suggested it
>appeared to be a formatting error in that perhaps some of the y's were
>uppercase or had a space in front of it. I went back through and made
>sure everything was uniform and now it appears that the formula is
>working!
>Thanks again and have a great day!!
>Kathy
>
>
>