How do I add up texts as fixed number values in a row?

J

john_8000

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.
 
T

T. Valko

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")
 
F

FrankWood

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
 
L

Lars-Åke Aspelin

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.
Click to expand...


You have to change the commas to semicolons, like this

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

Hope this helps / Lars-Åke
 
J

john_8000

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?
 
J

john_8000

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Help to copy information from one row at a time to another sheet 1
VLOOKUP with multiple look-up values 3
how do I get one dropdown menu to trigger another one in excel 2
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Drop down list options dependent on cell value 2
Change drop down list in one cell based on values in other cells 2
Insert row disappeared 1
drop down list and adding numeric values to text 2

Top