We've all used AutoFilter to sift through data right?
I've often struggled to be able to return a list of data based on a criteria -- by NOT using AutoFilter. The reason i'm against using AutoFilter is because you must have all the data on the page you're working with -- and the data you dont need is still there -- just hidden. I've seen others in the past use a Macro to cut out visible cells and paste to another page, etc...
Finally, from scouring the internet and putting pieces together, I came up with a pretty long forumla that will return a list of data based on a criteria. With the result, you can actually use to calculate with -- sum, avg, etc...
You can download a basic example of what I'm explaining @ http://www.mediafire.com/file/iwmfyit55oz/ListCriteria.xls
Be sure when you edit the formulas using INDEX, ROW and SMALL -- you are working with arrays so when you would normally press ENTER when finishing editing the cell -- you must press CTRL + SHIFT + ENTER. You may use the corner box to drag-down.
You dont necessarily need to understand how this formula works, just use the example spreadsheet and make it work for you.
Tyler B
