equate Text values to numbers for addition.

Originally Posted by Ron Coderre

See if this example does what you want...

With these values in A1:E2
Old Navy	Ross	TJX	Target	Walmart
26	8	8	4	4

And
Various Yes/No combinations under those values.

This formula assigns values to the Yes responses:
F3: =SUMPRODUCT(($A3:$E3="yes")*$A$2:$E$2)

Is that something you can work with?

Excellent..! You are my man...Ron Coddere


Can u please explain the code...I have attached the file for others who also can make use of this excellent piece of code....

SUMPRODUCT is really a unique funtion but have not absorbed it so well to use it too often...

If u have any good examples of when it can be used it would be wonderful to have a look...

Thanx once again..

Wow! I hardly know where to begin. I devote an hour to SUMPRODUCT in
the "Excel for Financial Analysts" class I teach...and it usually runs into
overtime. Why? Because SUMPRODUCT is Excel's "duct tape" function....it
works when practically nothing else will.

First, see SUMPRODUCT in Excel Help.

Basically, that function multiplies two or more same sized ranges
...then, sums the resulting products.

Example:
A1:B3 contains these values
4 7
5 8
6 9

If you wanted to multiply Col_A x Col_B, you'd get:
4 7 =28
5 8 =40
6 9 =54

The sum of those products is: 122

The SUMPRODUCT function, in this form, does exactly that:
=SUMPRODUCT(A1:A3,B1:B3)
=122

It also works in this form:
=SUMPRODUCT((A1:A3)*(B1:B3))
=122

Now, here's the fun part....You can put Boolean Expressions (fancy term for
TRUE/FALSE expressions) in the function to apply criteria to what's actually
added.

For instance, if we only wanted to add Col_B items where the corresponding
Col_A item is greater than 4...we could use this 2nd form of the function:
=SUMPRODUCT((A1:A3>4)*(B1:B3))

Each comparison of A1:A3 to 4 results in TRUE or FALSE.
Which Excel converts to 1 and 0, respectively, when multiplying.

So the source ranges become:
0 7 =0
1 8 =8
1 9 =9

And the SUMPRODUCT is: 17....(0x7+1x8+1x9)

For more information about SUMPRODUCT than you'd ever want to know
...see this link:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

I hope that helps.

Thanx a lot Ron Coddere I wish I cud attend one of your valuable classes..

Its not that i dont know SUMPRODUCT at all but i dont know what all it can do..because of its varied uses.

As hardly is mentioned in MS Excel help, you really cannot think it to be as an alternative to beat other functions as its not well-propagated.

Was really not sure whether to start a new thread or not?

I got the answer RON by using SUMPRODUCT.

However, in the earlier case there were three options
1. BLANK - SCORE -0
2. YES - SCORE - DEPENDING ON THE STORE.
3. NO- SCORE-0

Now , I have the stores which have been given "A","B","C","D" AND "E" for different parameters.

The point allocations is as follows:
A B C D E
10 8 6 4 2

Ex:
Sr.No Store Name Old Navy Ross TJX Target Walmart Total
1 abc A B C D A 28

Now these options are in the form of a drop-down, when i select "A" then the total should reflect 10 which is happening however when I select any alpahabet more than once it adds the score only once.

formula [ =SUMPRODUCT(($C2:$G2=$K$1:$O$2)*$K$2:$O$2) ]

$K$1:$O$2 -- contains the list..
A B C D E
10 8 6 4 2

can u suggest a solution?

Try this

=SUMPRODUCT(COUNTIF(C2:G2,$K$1:$O$1)*$K$2:$O$2)

Here's how that works...
With 
K1:O1 containing these values: A,  B, C, D, E
K2:O2 containing these values: 10, 8, 6, 4, 2

This part:
COUNTIF(C2:G2,$K$1:$O$1)
returns the counts of A's, B's, C's, D's, and E's in an array.

Here's an example of how it might look: {1,3,0,0,1}
That example indicates: 1_A, 3_B's, 0_C's, 0_D's, 2_E's

The COUNTIF array is multiplied by the values associated with 
each letter value (contained in K2:O2).

Still using my example:
={1,3,0,0,1} x {10, 8, 6, 4, 2}
={1x10, 3x8, 0x6, 0x4, 1x2}
={10, 24, 0, 0, 2}

And the SUMPRODUCT adds up those values to return: 36

I hope that helps.