Need Help with a challenging formula.

**gamiensrule** · 07-15-2010, 02:05 PM

I've been using excel for years and am very familiar with commonly used formulas, even some of the less common ones, but I'm having trouble figuring out how to do what I need to in this case - need to know if it's possible.

I need to evaluate several ranges of cells, pick out duplicate strings, and then sum values in other ranges based on these strings. For instance, search a range of cells- that contains three instances of the word "toy", three instances of the word "book", and two instances of the work "window". The three result fields would show "toy - *total of all toy fields* , book - *total of all book fields*, window- *total of all window fields* -etc.

I realize I may have to use several formulas to do this, for instance get the unique values first, then do a 'sumif ' - and that's not a problem. I just cant figure out how to look through the range and dynamically group values based on duplicates - WITHOUT me having to specify a search term.

Any ideas

**NBVC** · 07-15-2010, 02:07 PM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

PLEASE PM WHEN YOU HAVE DONE THIS AND I WILL DELETE THIS POST

**NBVC** · 07-16-2010, 10:05 PM

Thanks for taking the time to read and comply with the rule

.

Try a Pivot Table. It automatically filters for unique values and sorts them, then it sum, counts, averages, finds the max/min, etc... a powerful tool for this.

Let's say your table is columns A1:B100 with the categories in A and Amounts in B.

Go to Insert Pivot Table... click Next and ensure your range is captured, click next and Select where you want the data to go.. in current or new sheet.

Then click Ok.

Then in the pane that appears at the right.. drag the Category label from top section to Row Label area.. and drag the Amount item from top to Sigma (Values area)... if it doesn't show Sum of... then double click it and pick Sum.

http://www.timeatlas.com/5_minute_ti..._organize_data