=SUM(IF(C2:C500="Bell",IF(F2:F500="Yes",1,0)))


I have a spreadsheet that housing a list of providers and a list survey
questions. Columns F2 - F500 can be every "Yes" to "No". I tried to pick
out all the "Yes" answers for this provider and keeps getting a "1".

What am I doing wrong?


--
Beverly C.

=SUMPRODUCT(--(C2:C500="Bell"),--(F2:F500="Yes"))

This will return the count of records for which C2:C500 = "Bell" AND F2:F500
= "Yes" which is what I think you're trying to do.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Beverly C" wrote:

=SUM(IF(C2:C500="Bell",IF(F2:F500="Yes",1,0)))


I have a spreadsheet that housing a list of providers and a list survey
questions. Columns F2 - F500 can be every "Yes" to "No". I tried to pick
out all the "Yes" answers for this provider and keeps getting a "1".

What am I doing wrong?


--
Beverly C.

Beverly,

The formula you have will work if you array enter it (enter using Ctrl-Shift-Enter).

For a regular (non-array-entered) formula, try

=SUMPRODUCT((C2:C500="Bell")*(F2:F500="Yes"))

HTH,
Bernie
MS Excel MVP


"Beverly C" wrote in message
...
=SUM(IF(C2:C500="Bell",IF(F2:F500="Yes",1,0)))


I have a spreadsheet that housing a list of providers and a list survey
questions. Columns F2 - F500 can be every "Yes" to "No". I tried to pick
out all the "Yes" answers for this provider and keeps getting a "1".

What am I doing wrong?


--
Beverly C.

thank you so much -- I will try this.
--
Beverly C.


"Dave F" wrote:

=SUMPRODUCT(--(C2:C500="Bell"),--(F2:F500="Yes"))

This will return the count of records for which C2:C500 = "Bell" AND F2:F500
= "Yes" which is what I think you're trying to do.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Beverly C" wrote:

=SUM(IF(C2:C500="Bell",IF(F2:F500="Yes",1,0)))


I have a spreadsheet that housing a list of providers and a list survey
questions. Columns F2 - F500 can be every "Yes" to "No". I tried to pick
out all the "Yes" answers for this provider and keeps getting a "1".

What am I doing wrong?


--
Beverly C.

Did you complete the formula with ENTER or CTRL+SHIFT+ENTER since it is an
array formula?
But why not use SUMPRODUCT ?
=SUMPRODUCT(--(C2:C500="Bell"),--(F2:F500="Yes"))
and use just ENTER to complete it
For more info see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Beverly C" wrote in message
...
=SUM(IF(C2:C500="Bell",IF(F2:F500="Yes",1,0)))


I have a spreadsheet that housing a list of providers and a list survey
questions. Columns F2 - F500 can be every "Yes" to "No". I tried to pick
out all the "Yes" answers for this provider and keeps getting a "1".

What am I doing wrong?


--
Beverly C.

Entered as and array formula (CTRL + Shift + Enter) you are counting the
number of times Bell and Yes appear in the same row. Are you trying to do
something different?

"Beverly C" wrote:

=SUM(IF(C2:C500="Bell",IF(F2:F500="Yes",1,0)))


I have a spreadsheet that housing a list of providers and a list survey
questions. Columns F2 - F500 can be every "Yes" to "No". I tried to pick
out all the "Yes" answers for this provider and keeps getting a "1".

What am I doing wrong?


--
Beverly C.