Sum value of cells based on different array values

[bryceowen]

Hello again, everyone.

I have a sheet that I'm trying to generate a total on, but I'm having a time trying to figure out how to do it.

On this sheet, I have several rows of cells, one for a given employee. Each row is 30 cells long. In each of these cells, a "code" is entered (A, B, C, or D). Each code has a different point value associated with it that can vary depending on the month. There is a second sheet where I have two columns setup (B:C); the first being the code and the second being the value. What I want to do on the first sheet is have a cell on each row that gives a "point total" based on the codes entered.

For example, A=1, B=2, C=3, D=10. Employee Tom has codes A, A, A, A, B, B, A, D, A in his row thus far. The total should show 19.

I know this can be done with arrays, but everything I've tried just returns the result of the first cell in the row and not ALL of the cells.

Any help on this would be very much appreciated!

[tim201110]

LOOKUP can help

[Pete_UK]

Tell us which columns are used to hold those codes - is it column B to column AD?

Pete

[bryceowen]

The columns with the codes and values are B and C respectivly. (Column A is used to describe the codes purpose.)

[Pete_UK]

I meant the codes on the first sheet you describe. Attach a sample workbook, so we can see for ourselves.

Pete

[bryceowen]

example.xlsx
Here's an example workbook. The 'points' column on the first worksheet is what I'm trying to populate. I was able to accomplish this with a ridiculously long sum statement with a countif for each of the day's cells, but that's a lot of extra text that I really don't want/need. I'm sure there's a much simpler function.

[Pete_UK]

Yu can have this formula in B3:

=COUNTIF(C3:AF3,"A")*VLOOKUP("A",Formulas!B:C,2,0)+COUNTIF(C3:AF3,"B")*VLOOKUP("B",Formulas!B:C,2,0)+COUNTIF(C3:AF3,"C")*VLOOKUP("C",Formulas!B:C,2,0)+COUNTIF(C3:AF3,"D")*VLOOKUP("D",Formulas!B:C,2,0)

then copy down, although I'm sure that can be shortened further.

Hope this helps.

Pete

[WHER]

Alternatively in B3:
=SUMPRODUCT((C3:AF3=Formulas!$B$2:$B$5)*(Formulas!$C$2:$C$5))

[newdoverman]

This is what I came up with

Please Login or Register  to view this content.

If you use named ranges:

Please Login or Register  to view this content.

Both are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

These formulae will work even if the letters are determined by formula and errors occur.

[bryceowen]

Thank you all for your input! Wher, your solution worked best for me (and allows me to easily add additional codes on the formulas page).