I'm creating a row with drop-down lists with text.I want to create a simple
drop-down list with yes and no. Yes would have a background (unseen) value
of 1and no would have a value of 0. After items were selected from the
drop-down list, I want the unseen value totaled to the far right of the page.
I found the following formula which works for columns, but when I try to
change the cell range to a row of cells, I get "#N/A".


=SUMPRODUCT((C3:C23={"Yes","No"})*({1,0}))

Thanks for any assistance.

Since "No" has a value of 0 including it in a sum won't affect the result.
Since "Yes" has a value of 1 counting the number of "Yes" will be the same
as summing.

=COUNTIF(A1:J1,"Yes")

--
Biff
Microsoft Excel MVP


"john_8000" wrote in message
...
I'm creating a row with drop-down lists with text.I want to create a
simple
drop-down list with yes and no. Yes would have a background (unseen)
value
of 1and no would have a value of 0. After items were selected from the
drop-down list, I want the unseen value totaled to the far right of the
page.
I found the following formula which works for columns, but when I try to
change the cell range to a row of cells, I get "#N/A".


=SUMPRODUCT((C3:C23={"Yes","No"})*({1,0}))

Thanks for any assistance.

You might be able to do it easily with a count of the number of "yes"
responses.

=COUNTIF(G21:G24,"Yes")

Hope that helps.

Frank


"john_8000" wrote:

I'm creating a row with drop-down lists with text.I want to create a simple
drop-down list with yes and no. Yes would have a background (unseen) value
of 1and no would have a value of 0. After items were selected from the
drop-down list, I want the unseen value totaled to the far right of the page.
I found the following formula which works for columns, but when I try to
change the cell range to a row of cells, I get "#N/A".


=SUMPRODUCT((C3:C23={"Yes","No"})*({1,0}))

Thanks for any assistance.

On Thu, 10 Dec 2009 10:20:02 -0800, john_8000
wrote:

I'm creating a row with drop-down lists with text.I want to create a simple
drop-down list with yes and no. Yes would have a background (unseen) value
of 1and no would have a value of 0. After items were selected from the
drop-down list, I want the unseen value totaled to the far right of the page.
I found the following formula which works for columns, but when I try to
change the cell range to a row of cells, I get "#N/A".


=SUMPRODUCT((C3:C23={"Yes","No"})*({1,0}))

Thanks for any assistance.


You have to change the commas to semicolons, like this

=SUMPRODUCT((A1:J1={"Yes";"No"})*({1;0}))

Hope this helps / Lars-Åke

Thank you so much. That was much easier. Is there a place to go to find a
list or samples of excel functions and formulas?


"FrankWood" wrote:

You might be able to do it easily with a count of the number of "yes"
responses.

=COUNTIF(G21:G24,"Yes")

Hope that helps.

Frank


"john_8000" wrote:

I'm creating a row with drop-down lists with text.I want to create a simple
drop-down list with yes and no. Yes would have a background (unseen) value
of 1and no would have a value of 0. After items were selected from the
drop-down list, I want the unseen value totaled to the far right of the page.
I found the following formula which works for columns, but when I try to
change the cell range to a row of cells, I get "#N/A".


=SUMPRODUCT((C3:C23={"Yes","No"})*({1,0}))

Thanks for any assistance.

hi
see this site for 2003 functions...
http://office.microsoft.com/en-us/ex...042111033.aspx
see this site for 2007 functions...
http://office.microsoft.com/en-us/ex...791861033.aspx

regards
FSt1

"john_8000" wrote:

Thank you so much. That was much easier. Is there a place to go to find a
list or samples of excel functions and formulas?


"FrankWood" wrote:

You might be able to do it easily with a count of the number of "yes"
responses.

=COUNTIF(G21:G24,"Yes")

Hope that helps.

Frank


"john_8000" wrote:

I'm creating a row with drop-down lists with text.I want to create a simple
drop-down list with yes and no. Yes would have a background (unseen) value
of 1and no would have a value of 0. After items were selected from the
drop-down list, I want the unseen value totaled to the far right of the page.
I found the following formula which works for columns, but when I try to
change the cell range to a row of cells, I get "#N/A".


=SUMPRODUCT((C3:C23={"Yes","No"})*({1,0}))

Thanks for any assistance.

Excel functions are all shown in Excel help, with the exception of DATEDIF
(which is shown at http://www.cpearson.com/excel/datedif.aspx).

For most of the functions you'll find that Excel help has a "See also" link
to information on related functions.
--
David Biddulph

"john_8000" wrote in message
...
Thank you so much. That was much easier. Is there a place to go to find
a
list or samples of excel functions and formulas?


"FrankWood" wrote:

You might be able to do it easily with a count of the number of "yes"
responses.

=COUNTIF(G21:G24,"Yes")

Hope that helps.

Frank


"john_8000" wrote:

I'm creating a row with drop-down lists with text.I want to create a
simple
drop-down list with yes and no. Yes would have a background (unseen)
value
of 1and no would have a value of 0. After items were selected from the
drop-down list, I want the unseen value totaled to the far right of the
page.
I found the following formula which works for columns, but when I try
to
change the cell range to a row of cells, I get "#N/A".


=SUMPRODUCT((C3:C23={"Yes","No"})*({1,0}))

Thanks for any assistance.

Thanks everyone for helping me with my questions. Also, Lars-Ã…ke Aspelin,
thanks for the information on changing the commas. That will help me on
another spreadsheet where I have several values that I need totaled up.



"Lars-Ã…ke Aspelin" wrote:

On Thu, 10 Dec 2009 10:20:02 -0800, john_8000
wrote:

I'm creating a row with drop-down lists with text.I want to create a simple
drop-down list with yes and no. Yes would have a background (unseen) value
of 1and no would have a value of 0. After items were selected from the
drop-down list, I want the unseen value totaled to the far right of the page.
I found the following formula which works for columns, but when I try to
change the cell range to a row of cells, I get "#N/A".


=SUMPRODUCT((C3:C23={"Yes","No"})*({1,0}))

Thanks for any assistance.


You have to change the commas to semicolons, like this

=SUMPRODUCT((A1:J1={"Yes";"No"})*({1;0}))

Hope this helps / Lars-Ã…ke
.