Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

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
Asked:
kazmdav
1 Solution
 
Saqib Husain, SyedEngineerCommented:
It would be helpful to understand and test if you upload a sample workbook. You may upload something fake.
0
 
yuppyduCommented:
Either explain in greater detail your logic or upload a file. I have no idea what you need to do...
0
 
samrad1Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
kazmdavAuthor Commented:
Thanks samrad1, it' s spot on. Works great!  Thanks for your speedy response too.
0
 
kazmdavAuthor Commented:
Love the initiative!
0
 
byundtCommented:
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.

Brad
sum-listQ28300200.xlsx
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now