CODEWITHSUNDEEP
regexgoogle-sheetsfiltergoogle-sheets-formulacountif
Nicholas Schwinger
Nicholas Schwinger

Reputation: 13

How can I filter data in Google Sheets and include results that only match all conditions?

I have one main sheet titled Data Sheet that's basically set up like this:

Agent |Utilization Bucket|Activity
---------------------------------
Aaron |Team Meeting      |Other
Aaron |Team Meeting      |MTG_TeamMeeting
Aaron |Team Meeting      |Meeting
Aaron |Product Training  |TRN_ProductBT
Aaron |Product Training  |Training
Amanda|Team Meeting      |MTG_TeamMeeting
Amanda|Product Training  |TRN_ProductBT
Amanda|Product Training  |Training
Andrew|Team Meeting      |Other
Andrew|Team Meeting      |MTG_TeamMeeting
Andrew|Team Meeting      |Meeting
etc...

I then have individual tabs that are labeled Meeting, Training, and so on. What I'm trying to accomplish is filtering this on each tab so that it shows the activities that should be on that tab. For example, I want the Meeting tab to show only the activities "MTG_TeamMeeting" and "Meeting". However, there are some instances where an agent may only have just one of those, and not both of them, such as can be seen in the example above. Aaron has both of these activities, but Amanda does not.

When I try filtering to look for both activities it will show me literally every single instance of an agent having one of those activities, regardless of whether or not they have both of them.

I've tried many variations of the filter and query functions in order to get this to work without any luck. Examples:

=filter('Data Sheet'!A2:E1000,('Data Sheet'!C2:C1000="TRN_ProductBT")+('Data Sheet'!C2:C1000="Training"))

=QUERY(data, "select A,B,C,D,E where C = 'MTG_TeamMeeting' and C = 'Meeting'",-1)

These formulas can kind of get there, but it won't pull the output that I'm looking for. The filter formula above is the closest I've come, but it won't include only instances where there are both conditions being met. What I'm looking for is something like this:

Agent |Utilization Bucket|Activity
---------------------------------
Aaron |Team Meeting      |MTG_TeamMeeting
Aaron |Team Meeting      |Meeting
Andrew|Team Meeting      |MTG_TeamMeeting
Andrew|Team Meeting      |Meeting

Is there some way to be able to accomplish this? Do I need to sort something in a particular way or is there additional information that I should calculate to accomplish this?

Upvotes: 1

Views: 71

Answers (1)

player0
player0

Reputation: 1

=FILTER( FILTER(A2:C, REGEXMATCH(C2:C, "MTG_TeamMeeting|Meeting")), 
 COUNTIF(FILTER(A2:A, REGEXMATCH(C2:C, "MTG_TeamMeeting|Meeting")),
         FILTER(A2:A, REGEXMATCH(C2:C, "MTG_TeamMeeting|Meeting")))>1)

0

Upvotes: 1

Related Questions