• Status: Solved
• Priority: Medium
• Security: Public
• Views: 310

# Excel 2010 - Calculate a total value based on text selection

Hi,
Users will select from a data validation list (6 choices) in blocks of rows (in column C). Eg; rows 8 -18, 23-43, 48-60, 66-83.

I need to assign a different value to each of the choices (say 1 to 6) and calculate the total value for each block of rows & display that total in column D, next row down, eg; total for rows 8-18 will display in D19.

I have similar requirements are on other worksheets in same file, just the blocks of rows differ.

Your help will be greatly appreciated.
0
kazmdav
1 Solution

EngineerCommented:
It would be helpful to understand and test if you upload a sample workbook. You may upload something fake.
0

Commented:
Either explain in greater detail your logic or upload a file. I have no idea what you need to do...
0

Commented:
Hey! I took at stab at what i think you mean, though I'm just guessing so hopefully it helps. Used data validation and a smaller set of rows in the file attached. I assign numbers to the value to the left of the data validation based on a if statement.

The IF formula in column A refers to a list of possible data validation responses in E3:E5 :
=IF(B1=\$E\$3,1,IF(B1 = \$E\$4, 2,IF(B1=\$E\$5,3,"")))

The group total then adds up the values returned by the respective IF formulas.

sum-list.xlsx
0

Author Commented:
Thanks samrad1, it' s spot on. Works great!  Thanks for your speedy response too.
0

Author Commented:
Love the initiative!
0

Commented:
I realize that the question has already been answered, but if you would like to avoid using an auxiliary column for the IF formulas, then consider array-entering the following for your total:
=SUM((TRANSPOSE(B1:B5)=\$E\$3:\$E\$5)*(\$F\$3:\$F\$5))

The above formula uses the workbook posted by samrad1, but I added numeric scores for the dropdown choices in cells F3:F5.

The attached workbook is samrad1's workbook with his IF formulas modified to use values in F3:F5. I put the array-entered formulas in D6 and D12. You can see that they return the same results as the sum of the IF formulas.