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.
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.
Saqib Husain
It would be helpful to understand and test if you upload a sample workbook. You may upload something fake.
Either explain in greater detail your logic or upload a file. I have no idea what you need to do...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks samrad1, it' s spot on. Works great! Thanks for your speedy response too.
ASKER
Love the initiative!
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$
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.
Brad
sum-listQ28300200.xlsx
=SUM((TRANSPOSE(B1:B5)=$E$
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.
Brad
sum-listQ28300200.xlsx