Need formula instead of sorting

**JK1234** · 12-29-2009, 11:23 AM

I have a spreadsheet that has original data. I originally used the array formula after sorting. But my application is different now. I can not use the sort function or any macros so it has to be a function or filter. Been playing with this one for a while but cant figure it out. Please see file. Also the array wont work if there is a gap in the data. The data needs to be in the final list (green) in order and only one of each can show. Basically I want to show only one of each value or item and then find the sum of that value.

Thanks

**JK1234** · 12-29-2009, 05:51 PM

Just found out that I can not use an array formula.

**Palmetto** · 12-30-2009, 10:24 AM

Not exactly sure what you want. I suspect your sample workbook may not be truly reflective of your actual workbook. If it is not then the suggestions below may not be what you need, in which case upload a workbook that does reflect your real structure and type of data.

However, the attached show two possible solutions.
1. Formula: ="item "&ROW() -- and -- =SUMIF($B$19:$B$28,$F1,$C$19:$C$28)

2. Pivot Table (no blank rows or columns allowed)

**JK1234** · 12-30-2009, 12:26 PM

Thanks for response but I can not use pivot tables either.

And the other version does not work either. The file must work with blank spaces.The actual file starts at B18:C30

Then what i need is them in order like G19:H22.

I will have blank spaces and need them to print out (in order) only one instance of each and then total that.

**Palmetto** · 12-30-2009, 12:45 PM

You're really limiting yourself by not using Pivot Tables and performing a sort on the data. Why these limitations (or preferences)?

I see your results table is in alphabetical order and this is not easily accomplished with a formula, especially given multiple entries exist.

You could used Advance Filter to extract a list of unique values (for the item names), copied to a new location and then perform the sum using SUMIF.

Or, considering sorting the original data in place, forcing blanks to the bottom and then using the built-in SUBTOTAL feature which will allow you to collapse the rows and show only the totals.

I don't understand why you aren't taking advantage of the applicable tools to get the results you need. Use a separate worksheet if necessary so that the original data is untouched.

**JK1234** · 12-30-2009, 01:20 PM

I am using the excel file to create other pages - like HTM. So the excel version is for some and htm is for others. And there is alot to maintain so doing one in excel in one way and the other in another wont work. Alot easier to build in excel and then transposed page over into htm or asp.
So what works in excel has to work in htm.

This is actually working well except some of the functions wont convert/switch over so my I am looking for functions instead of my vb/tables/macros that I am using.

I think this problem may not get fixed for this page.