How can I write a formula for: cell=+1*number of selections

[JeffJ]

In E23, I have the following formula:

=IF(AND(OR(E7="E-120-D",E8="E-120-D",E9="E-120-D",E10="E-120-D",E11="E-120-D",E12="E-120-D",E13="E-120-D",E14="E-120-D",E15="E-120-D",E16="E-120-D"),OR(E6="38X12",E6="40X12",E6="42X12",E6="44X12",E6="46X12",E6="48X12",E6="50X12",E6="52X12",E6="54X12",E6="56X12",E6="58X12",E6="60X12",E6="62X12",E6="64X12",E6="66X12",E6="68X12",E6="70X12"),E2="...3/12...",E3="1
inch"),1,"")

E7:E16 each contains a drop-down list, and each of those drop-dow
lists contain the same choices. These represent options you can choos
to be added on.

The way the formula is set up now, it returns "1" in E23, whether yo
select "E-120-D" in only one cell (of E7:E16), or whether you selec
"E-120-D" in all ten of the cells E7:E16. This is not what I want. I
you select it in all ten cells, I want E23 to read "10," not "1."
want it to add 1 for each selection of "E-120-D" in E7:E16. In othe
words, if you select "E-120-D" in E7 and also select it in E8, I wan
E23 to reflect "2", not "1". Or, if you select "E-120-D" in E7, an
again in E8, and again in E9, and again in E10, I want E23 to read "4"
not "1."

How can I adjust my formula to do that?
Is there a way to use VLOOKUP to accomplish this?
(Unfortunately, I don't know VBA, VBS, or ASP.)

SideNote: I would rather not have to write a formula containing te
arguments or "IF" functions (which, although I realize IF functions ar
limited to 7 statements, I believe using the CONCATENATE functio
allows you to supposedly go up to 30 arguments---or better yet, th
concatenation operator "&", which supposedly bypasses even 30), whic
would be long and complicated, and which would probably give me
"Formula too long" error because of all the AND and OR functions i
would have to contain.

Thank you for any reply.

-Jeff
(e-mail address removed)

 

[Pet e McCosh]

Jeff,

=Countif(E7:E16,"=E-120-D")

Cheers, Pete.

-----Original Message-----

 

[JeffJ]

I'm getting closer, but it's still not working; I'm getting a weir
result. As a possible solution to my previous post, I tried:

=IF(AND(OR(E7="E-120-D",E8="E-120-D",E9="E-120-D",E10="E-120-D",E11="E-120-D",E12="E-120-D",E13="E-120-D",E14="E-120-D",E15="E-120-D",E16="E-120-D"),OR(E6="38X12",E6="40X12",E6="42X12",E6="44X12",E6="46X12",E6="48X12",E6="50X12",E6="52X12",E6="54X12",E6="56X12",E6="58X12",E6="60X12",E6="62X12",E6="64X12",E6="66X12",E6="68X12",E6="70X12"),E2="...3/12...",E3="1
inch"),IF(E7="E-120-D",1,"")&IF(E8="E-120-D",1,"")&IF(E9="E-120-D",1,"")&IF(E10="E-120-D",1,"")&IF(E11="E-120-D",1,"")&IF(E12="E-120-D",1,"")&IF(E13="E-120-D",1,"")&IF(E14="E-120-D",1,"")&IF(E15="E-120-D",1,"")&IF(E16="E-120-D",1,""))

However, instead of adding 1 for every instance of E7:E16 (selectin
"E-120-D" in both E7 and E8 results in 2, etc.), it adds another digi
of 1. For instance, if I select "E-120-D" in both E7 and E8, I get 11
If I select "E-120-D" in E7, E8 and E9, I get 111. If I select E7
E8, E9 and E10, I get 1111.

Still looking for a solution

 

[JeffJ]

Pete,

COOL! Thanks very much for that solution, and for helping me out! I
works!

=IF(AND(OR(E6="38X12",E6="40X12",E6="42X12",E6="44X12",E6="46X12",E6="48X12",E6="50X12",E6="52X12",E6="54X12",E6="56X12",E6="58X12",E6="60X12",E6="62X12",E6="64X12",E6="66X12",E6="68X12",E6="70X12"),E2="...3/12...",E3="1
inch"),COUNTIF(E7:E16,"=E-120-D"),""