Excel Array Formula: Count Rows based on OR condition - Excel MMULT, INDIRECT Functions (Part 3/3)

In this video I build on the Matrix Multiplication (MMULT) formula - from Part 2. In the MMULT formula, I used the TRANSPOSE function to be able to dynamically add new columns to the original list. The problem with TRANSPOSE is that it requires Control shift Enter (CSE). In the best case, I'd rather have a formula that doesn't require CSE.

In Part 3, I replace the TRANSPOSE part of the MMULT formula with alternate formulas. The idea is to use the ROW function, but somehow get the rows to be dependent on the columns.

I first set out to use the ROW and INDIRECT functions. Then I use the ROW, INDIRECT and ADDRESS Function and finally I don't use INDIRECT at all, but instead the ROW and INDEX function (This one became my favorite). The great thing about the INDEX function is that it returns a cell reference instead of a cell value if you use it after the : sign in a formula. (INDEX is an amazing function!). Do look into it in more detail if you don't know it well.

🚩Let’s connect on social:

[wayneedmondson]

Hi Leila.. this is an excellent 3-part series for anyone wanting to understand array formulas and matrix manipulations. I learned a lot. I particularly liked your trick with A1:INDEX() to build a dynamic array2 for MMULT and solve the problem of future column inserts in the data. Also, the insights on how and why CSE is not needed for some of the solutions was very interesting. Lots to think about. Thanks for Part 3 of 3. I'm sure I'll revisit all 3 parts again in the future. Triple thumbs up!!!

[excelisfun]

Thank you for your video - especially the last part where you reveal that most Array Functions that are placed in aggregate functions don't require Ctrl + Shift + Enter, except for that stubborn TRANSPOSE Array Function!!

You have created some great cool formulas!!!

I also like your use of TRANSPOSE(COLUMN()) without the ^0 (in this formula: =SUM(--(MMULT((B19:D68=P2)+(B19:D68=P3)+(B19:D68=P4), TRANSPOSE(COLUMN(B18:D18)))>0)) ) because it does not matter for the resultant array of numbers because anything greater than zero will work as a count.

I think I like this one the best: =SUM(--(MMULT((B19:D68=P2)+(B19:D68=P3)+(B19:D68=P4), ROW(A16:INDEX(A16:A22, COLUMNS(B18:D18))))>0)) because it has a clever, INDEX as a cell reference. Love this formula Element: ROW(A16:INDEX(A16:A22, COLUMNS(B18:D18))) : )

In the end, I often think that because they are array formulas, I usually like the one that will calculate most quickly. I did not time them on a big data set, so I am not sure…

I have a question: How is this a Unique Count Problem? Isn't this an Or Logical Test Problem, where the question is "how many records contain at least one of the listed companies?" The data set does not appear to have any duplicate records. If you add a duplicate, like Company B Company R Company K as the third record, the count goes from 29 to 30, even though there is now a duplicate and the unique count would be 28.

Amazing work, Leila Gharani!

[JNguyenKnight]

I'm learning so much out of these 3 videos. They've opened up a whole world of possibilities, definitely getting bookmarked for regular review.

[mahbubhossain]

Great tutorial !!! You have explained different ways of solutions. Most amazing part was the maze of the Array functions!!!!

[salifdiallo]

Thanks a lot ! Je vais devenir carrément expert grâce à toi ;)

[vida]

Hi Leila. Thanks for your videos. The quicker way to solve this problem is Power Query. You just need to unpivot the last 3 columns and then filter out unwanted companies, finally remove duplicates from the Workshops column and you get the final list.

[RAHULfromIND]

Superb.... just to know..are you an Indian?

[michaelconnors]

Here's VBA code that does the the same thing.
Option Explicit

Sub RemoveDuplicatesInRow()

Dim lastRow As Long
Dim lastCol As Long
Dim R As Long 'row index
Dim c As Long 'column index
Dim i As Long, Cell As Range

Dim Target As Range, rng As Range
Dim F1 As String, F2 As String, R1 As String

F1 =
F2 =
R1 =


Application.ScreenUpdating = False
Application.DisplayAlerts = False

Range("Table1[[#Headers], [Workshops]]").Select
Application.Goto Reference:="Table1"
Selection.Copy
Sheets.Add.Name = "Temp Sheet"
Range("B4").Select
ActiveSheet.Paste

With Sheet2
'AcitiveSheet.UsedRange
Set Target = Sheets("Temp Sheet").Range(Range("C4"), Range("E4").End(xlDown))
For Each Cell In Target
If Cell.Value = F1 Then Cell.Value = R1
Next Cell
For Each Cell In Target
If Cell.Value = F2 Then Cell.Value = R1
Next Cell

With ActiveSheet.UsedRange
lastRow = .Row + .Rows.Count - 1
lastCol = .Column + .Columns.Count - 1
End With

For R = 1 To lastRow
For c = 1 To lastCol
For i = c + 1 To lastCol 'change lastCol to c+2 will remove adjacent duplicates only
If Cells(R, i) <> "" And Cells(R, i) = Cells(R, c) Then
Cells(R, i) = ""
End If
Next i
Next c
Next R
Worksheets("Temp Sheet").Range("H4").Formula = "=COUNTIF($C$4:$E$100, UniqueCountRows!P3)+COUNTIF($C$4:$E$100, UniqueCountRows!P4)+COUNTIF($C$4:$E$100, UniqueCountRows!P5)"
= Range("H4")

Range("Q6").Select

Sheets("Temp Sheet").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End With
End Sub