How to use Countifs,Or and Sumproduct efficiently

Question

I have a list of accounts with 2 digit modifiers. Some accounts will have more then one modifier. I am looking for accounts with a certain combinations of modifiers.

So I have a list of accounts in the B column.

I have the modifiers in C Column

Example

Act #   Modifier
111     80
111     56
111    
222     55
222  
333     51
333     50
333

I have some working code that works great until I get to many rows.

In this sample formula I have 8 Modifier groups.

50,22,51,62
51,22,62
54,50,51
55,50,51
56,50,51
80,50,51
"AS",50,51
59,50

=IF(OR(SUMPRODUCT(COUNTIFS(B:B,B3,C:C{50,22,51,62}))>=2,SUMPRODUCT(COUNTIFS(B:B,B3,C:C,{51,22,62}))>=2,SUMPRODUCT(COUNTIFS(B:{54,50,51}))>=2,SUMPRODUCT(COUNTIFS(B:B,B3,C:C,{55,50,51}))>=2,SUMPRODUCT(COUNTIFS(B:B,B3,C:C,{56,50,51}))>=2,SUMPRODUCT(COUNTIFS(B:B,B3,C:C,{80,50,51}))>=2,SUMPRODUCT(COUNTIFS(B:B,B3,C:C,{"AS",50,51}))>=2,SUMPRODUCT(COUNTIFS(B:B,B3,C:C,{59,50}))>=2),"Check","")

This code will put check by any account that has 2 or more of the modifiers from any of the 8 groups. It has to be 2 modifiers from the same group though.

I was just wondering if there is a better way to write this? Instead of doing all these or can I just do OR for the different modifier criteria I am looking for?

Something like

=COUNTIFS(H:H,H5,I:I,OR({59,50},{"AS",50,51}))

Answer 1

As requested by @SkysLastChance, I will post my solution using Power Query (PQ) even though the question was tagged to Excel-Formula.

Please note you MUST use Excel 2010 or later versions otherwise you will not be able to use Power Query. My answer might not be robust enough for people who has not used PQ before. So feel free to leave a question if you are unclear with any particular step.

Step 1

Convert the Account List and Modifier Group in the example into Table in your excel worksheet. One way of doing that is to highlight the data including headers and press Ctrl+T. Then you should get two tables as shown below. I have named the first table as Tbl_ActList, and named the second one as Tbl_MoGrp.

Please note I have added some data to the Account List table for result testing purpose.

Step 2

Select any cell within a table, go to the Data tab on top of your excel (mine is Excel 2016), click From Table in the Get & Transform section. It will load and add the table to the built-in PQ Editor. You can exit the editor (and keep the changes), and repeat this step to add the second table to the PQ Editor. Alternatively you can add a new query in the PQ Editor and find the second table from your excel worksheet. I will not demonstrate this process as you can google the know-how later on.

Step 3

Once you have added both tables to the editor, you can start editing/transforming data in each table/query using built-in functions and/or advanced coding. In this case I only used built-in functions.

For the Modifier Group table, I want to transform the original data into a 2-Column list with one column showing which Group the modifier belongs to, and the other column showing a single modifier.

Firstly, use the Split Column function in the Transform tab to split the original modifier groups into single value by using , (comma) as the delimiter.

The new table is in matrix structure which is no ideal for look up purpose, so I used Unpivot Columns function in the Transform tab to convert it into list structure. What I actually did is to highlight the Grp column and select Unpivot Other Columns to get the list. Alternatively you can highlight the first four columns and use Unpivot Columns to get the same list.

Then I renamed Value column as Modifier, and removed the Attribute column to end up a 2-Column table.

Please note all data in each table/query in this example have been set to 'Text' format (aka data type). Data type is very sensitive and specific in PQ, and incorrect data type may lead to error.

Here is the full code behind the scene. All steps are performed using the built-in functions without any advanced coding:

let
    Source = Excel.CurrentWorkbook(){[Name="Tbl_MoGrp"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Modifier", type text}, {"Grp", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Modifier", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Modifier.1", "Modifier.2", "Modifier.3", "Modifier.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Modifier.1", type text}, {"Modifier.2", type text}, {"Modifier.3", type text}, {"Modifier.4", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Grp"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Value", "Modifier"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"})
in
    #"Removed Columns"

Step 4

With the Modifier Group list ready, we can look up the modifier group in the Account List table for each modifier using Merge Queries function in the Home tab. The logic is to find the link between two tables to conduct a look up.

Firstly, select/highlight the column (Modifier) that contains the look up value in the origin table (Tbl_ActList), then select the table (Tbl_MoGrp) that you want to look up from, then select/highlight the corresponding column (Modifier) in the second table, and then click OK to continue.

Please note before merging I have filtered the Modifier column in the Account List table to get rid of cells showing null (blank) as they are not useful for the look up.

After merging the queries there is a new column added to the Account List table. It may look like a column but it contains all data from the Modifier Group table stored in Grp column and Modifier column. As we want to look up the modifier group only, we can Expand the column to show the Grp column only.

Click on the little square box on the right hand side of the header of the last column to trigger the Expand function, then select the Grp column only and click OK to continue.

Now we have a table showing account number, modifier, and modifier group. We can then use the Group By function in the Home tab to find out for each account number how many modifiers have appeared in each applicable modifier group.

Please See below screenshot for the settings for the Group By function.

Then I sorted the table ascending by Acc # column, and filtered the Count column to show values greater than or equal to 2, i.e. at least 2 modifies linked to that account number have appeared in a modifier group.

Here is the full code behind the scene:

let
    Source = Excel.CurrentWorkbook(){[Name="Tbl_ActList"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Act #", type text}, {"Modifier", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Modifier] <> null)),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Modifier"}, Tbl_MoGrp, {"Modifier"}, "Tbl_Grp", JoinKind.LeftOuter),
    #"Expanded Tbl_Grp" = Table.ExpandTableColumn(#"Merged Queries", "Tbl_Grp", {"Grp"}, {"Grp"}),
    #"Grouped Rows" = Table.Group(#"Expanded Tbl_Grp", {"Act #", "Grp"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Act #", Order.Ascending}}),
    #"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each [Count] >= 2)
in
    #"Filtered Rows1"

Step 5

The answer could stop at Step 4 as the table has shown the account number that we are looking for. However if there are thousands of account numbers, then it is better to Remove Other Columns except the Act # column, and Remove Duplicates within the column, and then Close & Load the result to a new worksheet. The final result may look like this:

A tip here, before Close & Load any query for the first time, it is better to set the following in your Query Options. It will prevent PQ Editor to load each of your queries to a separate worksheet by default. Just imaging how long it will take if you have 20 queries in your PQ Editor and each of them have more than a thousand lines of data.

Once you change the default option, PQ Editor will only create connections for your queries after you click Close & Load, and you can manually load a specific query result to a worksheet as shown below:

Conclusion

I believe if this question was tagged as a PowerQuery, there may be more concise or 'fancier' answers than mine. Regardless, the things that I like PQ the most are it is a built-in function of excel (2010 and later versions), it is scalable, replicable and more powerful when it comes to data cleansing and transforming.

Cheers :)