Forum Discussion
Selecting Data Based on Multiple Criteria
I need an Excel Formula Wizard to help me with data selection. Following is an example of my data: It is a list of leads that have been received through two different marketing channels (ECO Portal and Seminars and Roadshows). This is what I'm trying to accomplish: If an 'Account Name' has leads from both of the 'Lead Generators' of 'Eco Portal' and 'Seminars & Roadshows' (not just one of them) and the 'Closing Date' falls within the date ranges of the 'Created Date' and the 'Plus 6 mos' date, and the 'Status' is 'Won', then select the data and include 'Sales $' in total. Data should be summarized by Account Name. My spreadsheet has 32K records, so it is extremely slow going through these one by one to get what I am looking for.
Thank you!
I think you'd find the FILTER function able to accomplish this. Here's a link to a very good explanation of FILTER. And here's a video produced by Microsoft when the function was first introduced.
If you want further help in writing the formula, please provide an actual file--maybe just the top 150 rows of your 32K rows--so we could work with the real thing without having to re-create it. If you can't post it here using drag and drop, then post it on OneDrive or GoogleDrive with a link pasted here that grants access.
I don't seem to be able to share my file. I think my company has a firewall preventing the upload, I was able use the filter formula =FILTER(A2:R27940,(E2:E27940=T2)*(F2:F27940=T3),"") to set up a list by lead generator, but I don't know how to deal with the fact that the same account is on multiple lines.
I'll keep working on it, and in the meantime, I will keep going through the list manually :(
Thank you!
It's not in sync with you initial question, FILTER doesn't provide any aggregation. You may use PivotTable, desirably with using data model if it is supported. Or, perhaps and if available, GroupBy/PIVOTBY functions.
Without the file it's hard to be more concrete. Company most probably prohibits to share the file with everyone, but you may use personal free OneDrive or Google Drive and share on it, removing all sensitive information from the file.
Attach a sample file to your post. You may also share the file via Google-Drive or OneDrive.
Are you able to open the above spreadsheet?
Not able to open it, no. That site is asking for an account and password. GoogleDrive and OneDrive allow you to post the file in such a way that you grant the access so that others can see the file without a password.
It ask for login. Share the file as public so that we can download without login.
https://filetransfer.io/
Share by this cloud drive
You will have to make it fit your table but I kinda went by what you had. I had an issue with the dates, kept getting strange errors, so I used "less than 180" days in place of "6 months". That made it function correctly. You will have to replace the "#" in your spreadsheet with nothing. Works with blank cell but if there something besides a date in the "Date Closed" column it blows up.
First formula is just a total filter of the table. Lots of extraneous info in the straight filter.
Second formula gets rid of extraneous info but still just a filter. Bracketed numbers will pull out only the columns you want from the array.
Third formula formula totals by account and lead and status. If you have the same Account Name under different Account #'s it will spill and total for each Account #. First time I've ever worked with the =Pivotby and =Take functions. Don't ask me how they work...Google is my friend.
I used =Unique() to get all unique accounts, leads and status entries. Placed the results in pulldowns to reference with the formulas. Allows quickly filtering everything through all the variables. Thank you for this question, I learned a lot while trying to figure it out. Hope one of these is what you were looking for.
Copy and paste Formulas: (you shouldn't have to try and type from the picture above)
=FILTER(B2:I7,(D2:D7=B17)*(C2:C7=B19)*(H2:H7=B18)*(G2:G7-E2:E7<=180),"No Matching Data")
=FILTER(INDEX(B2:I7,SEQUENCE(ROWS(B2:I7)),{1,2,3,7,8}),(C2:C7=B19)*(D2:D7=B17)*(H2:H7=B18),"No Matching Data")
=iferror(LET(h,FILTER(B2:I7,(D2:D7=B17)*(H2:H7=B18)*(C2:C7=B19)*(G2:G7-E2:E7<=180)),PIVOTBY(TAKE(h,,3),,TAKE(h,,-1),SUM,,0)),"No Matching Data")
Thank you for working through this for me. I plugged in the original formula you sent me, and I got a #NAME? error. I'm sure it is something on my end, and I think the screen shot you attached will help me. I'm going to keep working it. I haven't had time to open a personal Google Drive account to try to upload a file. I'm sure that would help everybody who has responded. As mentioned, I'll keep working on it and post again when I get closer. Thank you!
It may be trying to reference those pulldowns on my spreadsheet. Place a ' at the front of the formula to turn it into text. Then you can edit manually. When you think you have it right, delete the ' and see if it works.
