Filtering a large list to use SUMIF formula
Posted by Dave D on January 09, 2002 6:45 AM
Trying to build an excel expense report were if and expense is related to a project the will choses one(from several hundred) That is the easy part. My problem is that at the bottom I want to show subtotals by project. I know the sumif will give me the subtotals if the use a project more than once. My problem is how do I get a filtered list of the projects they chose down at the bottom of the report. Rather than listing all 100 projects below with a sum if next to it because we could add new project through the year and I would constantly be maintaining a list. A macro?
Posted by Scott on January 09, 2002 7:09 AM
Where would the list of products that the user is choosing be? My first suggestion would be the subtotal function, but I don't think this is what you're looking for.
Posted by Dave D on January 09, 2002 8:00 AM
Well the employee would know the project # but I could get fancy and add a drop down list, but I do not want to maintain that as we add new projects trough the year. What i need are subtotals below the expense report data entry area. Should a build a macro that grabs that area pastes it to a new location and subtotal?
Posted by Aladin Akyurek on January 09, 2002 8:21 AM
Dave --
If you already have a project list from which a project is selected (via a dropdown list created thru data validation) and the selection is feeded to a SUMIF formula as criterion, I'd suggest arranging in such a way that any nw project that is added to the list also appears automatically in the dropdown list.
Would that help?
Aladin
Posted by Dave D on January 09, 2002 11:02 AM
The problem is how do I get just the projects they selected down below for my SUMIF formula to reference. I need something that grabs all the projects they listed and distills them down to get rid of duplicate projects and places this list in another area of the workbook so my sumif can give total charges by project.
Posted by Aladin Akyurek on January 09, 2002 11:23 AM
Do they chose a) 1 or b) multiple projects?
If (a) is the case, it would not be difficult to feed the selection to s SUMIF formula that computes a total for that project, provided that there is data in data area/rnge involving the selected project.
If (b), I think you'd probably use VBA (depending on the method by which multiple projects are selected) to put selected project names below you report adjacent to a vertical range where SUMIF formulas can pick up project names and do the necessary computations.
Hope this helps.
Aladin
Posted by Dave D on January 09, 2002 12:20 PM
This may help
line one of there expense report is a flight 600$ and the would choose only one project. The next line may be dinner out on a different project. the third line may be another flight with the same project as selected for the first flight.
Rather than listing all project below with a sum if formula next to them I was hoping I could use some macro to read the lines the used and put the projects someplace below with subtotals by project.
Posted by Aladin Akyurek on January 09, 2002 12:37 PM
It darn helps. I think I'm getting some idea of what is required. A formula-based solution is possible (if not too many "lines", the performance will be acceptable).
I don't know your lay-out: Care to post some sample data (or send me your workbook) along with expected results?
Aladin
