# Tags: TRANSPOSE

Here is a simple matrix like data layout which shows the features available in every product.  Let's assume that this data is in range A2:E8 (including the header row)

 Functions Product A Product B Product C Product D Function 1 x Function 2 x x Function 3 x x x Function 4 x x Function 5 x x Function 6 x

Here is a user created input table of his/her requirements.  Let's assume that this data is in range A12:B18 (including the header row)

 Functions Input Function 1 Function 2 x Function 3 x Function 4 Function 5 x Function 6

The expected result is the "Product" which meets the user defined function combinations. The result should be Product B. If there are different products which meet the user's requirements, only the first one will be returned as a result. If one would like all products to be returned, the one can use Power Query to resolve this problem.

=IFERROR(INDEX(\$B\$2:\$E\$2,1,MATCH(COUNTA(\$A\$13:\$A\$18),MMULT(1*(TRANSPOSE(B3:E8=B13:B18)),1*(ISNUMBER(ROW(INDIRECT("1:"&COUNTA(\$A\$3:\$A\$8)))))),0)),"No such product")

Please note that this is an array formula so please confirm the formula with Ctrl+Shift+Enter.

Imagine a dataset as shown below

 Status Text Number Active bat 1 Passive erq 2 Passive cat 3 Active enm 4 Active dog 5

Suppose the data is in range A3:C7 (headings are in A2:C2).  The objective is to add numbers where the following conditions are met:

1. Status should be Active; and
2. Text should contain either b or c or d

So the result for the dataset above should be 6 i.e. 1+5.  Sounding like a simple case of summarising based on multiple conditions, one may attempt using the SUMIFS() function here.  However, a single SUMIFS() function will not work because a SUMIFS() can only accept multiple AND conditions (not OR conditions).  The only way to make the SUMIFS() function work is to use 3 of them and then add them up.

So this SUMIFS() function will work

=SUMIFS(\$C\$3:\$C\$7,\$A\$3:\$A\$7,A11,\$B\$3:\$B\$7,B11)+SUMIFS(\$C\$3:\$C\$7,\$A\$3:\$A\$7,A12,\$B\$3:\$B\$7,B12)+SUMIFS(\$C\$3:\$C\$7,\$A\$3:\$A\$7,A13,\$B\$3:\$B\$7,B13)

A11:A13 has Active and B11:B13 has *a*,*b*,*c*.

While this looks like a solution, the problem is that there will have to be one SUMIFS() function for each OR condition.  So the more the number of OR conditions, the more the SUMIFS() functions and the lengthier the formula.

In this workbook, I have shared two alternative solution.  One uses the DSUM() function and other uses a combination of MMULT(), TRANSPOSE() and SUMPRODUCT() functions.

In a list with multiple occurrences, the VLOOKUP() function in Excel will only return the first occurrence of the string being searched.  Furthermore,  the VLOOKUP() function returns data from one column only.

To work with a list where there are multiple occurrences of a particular string and data has to be extracted from multiple columns, you may refer to my solution in this workbook.

To work with a list where there are multiple occurrences of a particular string and data has to be extracted from one column only, you may refer to my solution at this link.

A number is entered in cell A1 of every sheet in a multi sheet workbook.  The task is to sum numbers in cell C2 of the Summary sheet from cell A1 of last x sheets - x is the number of sheets (mentioned in cell B2 of the Summary sheet) from which numbers have to be summed up.  So if the number entered in cell B2 of sheet is 3, then sum numbers from cell A1 of last three sheets.  Worksheets can get added, removed or renamed.

You may refer to my solution in this workbook.

Assume a "Labour utilisation" report which shows day wise tasks performed by different employees (row labels are task names, column labels are days and data in the matrix shows employee names).  Each employee can perform multiple tasks on the same day and same task on multiple days.

Given a list of all employees, one may want to answer the following questions:

1. Days worked for each employee - If an employee performs multiple tasks on the same day, then that shall be counted as only 1 day of work

2. Tasks performed across all days - If an employee performs the same task across multiple days, that that shall be counted as only 1 task across all days

You may refer to my solution in this workbook.

You may refer to a similar problem at the following link - "Analysing customer walkin data by date and service taken"

Assume the following sentences in range B2:B6

B2 - This is an Apple Pie
B3 - An apple a day keeps a doctor away
B4 - These Pears, apples and mangoes are sweet
B5 - In this season, prices of mangoes have increased
B6 - This is a glass of Guava juice

In range B9:B10, type Mango and Apple.  The objective is to count the sentences in range B2:B6 which exclude the words mentioned in range B9:B10.

The array formula (Ctrl+Shift+Enter) for getting the count of exclusions would be:

=SUMPRODUCT(1*(MMULT(TRANSPOSE(1*(ISTEXT(\$B\$9:\$B\$10))),1*(ISERROR(SEARCH(\$B\$9:\$B\$10,TRANSPOSE(\$B2:\$B\$6)))))=COUNTA(\$B\$9:\$B\$10)))

The result should be 1.

The array formula (Ctrl+Shift+Enter) for getting the count of inclusions (sentences which include these words), would be:

=COUNTA(\$B\$2:\$B\$6)-SUMPRODUCT(1*(MMULT(TRANSPOSE(1*(ISTEXT(\$B\$9:\$B\$10))),1*(ISERROR(SEARCH(\$B\$9:\$B\$10,TRANSPOSE(\$B2:\$B\$6)))))=COUNTA(\$B\$9:\$B\$10)))

The result should be 4.

One can also solve the problem with the PowerPivot tool.  You may refer to my PowerPivot solution in this workbook.

Assume a database of five rows and four columns.  Each cell of the database has some text value - let's call this the source grid.  In another part of the same worksheets, the text values of source grid have been arranged as rows and column headings - let's call this the destination grid.

The task is - "To display in the blank cells of the destination grid, total count of row and column heading combinations in each column of the source grid.".  So the counting of row and column heading combinations has to happen in each individual column of the source grid and then all these numbers should be summed up.

The source grid, expected result and formula in individual cells of the destination grid haven been shown in this workbook.