Forum Discussion
Excel Challenge
I have big problem in writing
Excel formula for the following problem:
I have Three feature of my costumer I want to separate my costumers by these
three features and give them Index 1, 2 and 3,
For Costumers which they have A feature give 1 index
For costumers which they have B feature give 2 Index
and for the costumers which they have A and B (both of them) give the 3 index
also I send attachments,
How I can do this in Excel ?
I appreciate for your guide in advance,
=MMULT(--(COUNTIFS($B$2:$B$17,B2,$A$2:$A$17,{"A","B"})>0),{1;2})
8 Replies
=MMULT(--(COUNTIFS($B$2:$B$17,B2,$A$2:$A$17,{"A","B"})>0),{1;2})MMULT is a function I'd not seen before.
Frankly, the Help text on the function isn't very helpful in understanding it. Can you explain how it's working here to solve the question asked by @Jalal_1988
I'm not good at explaining MMULT(). So I have to refer to the support page. The key text is: The result is an array with the same number of rows as array1 and the same number of columns as array2.
array1 has one row (and two columns) and array2 has one column (and two rows). So the output is an array with one row and one column.
Are you able to reorganize the data to a cleaner table, as in the attached? If so, a simple IFS formula gives the resulting "Index" value. In general, if it's possible for all your data, you're better off having any one customer occupy only one row. Especially where the only differences have to do with one or two elements of information, because they can be handled by different columns on the same, single row.
If there's some other compelling reason to have multiple rows per person, please spell that out. You might then be better served by having two or more tables, connecting them via Power Query or some other feature.
I've attached the spreadsheet, but here's an image for your review. The formula is visible as well. That formula is just copied down to the other rows.
