Excel Challenge

[Jalal_1988]

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,

[#2]

@Jalal_1988 

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.

[#3 · Accepted solution]

@Jalal_1988 

=MMULT(--(COUNTIFS($B$2:$B$17,B2,$A$2:$A$17,{"A","B"})>0),{1;2})

[#4]

@Detlef Lewin 

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

[#5]

@mathetes 

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.

[#6]

@Detlef Lewin  that is a clever solution

@mathetes  mmulti() stands for matrix multiply which goes back to linear algebra days.  

I started to try and explain it here but you would be much better served with a website designed to teach it like this

[#7]

@mtarler 

Thanks for that link. I'm printing the page now for careful study.....and then I'll keep my eyes open for opportunities to apply MMULTI. It's in the using that it'll really (finally) make sense.

[#8]

@mathetes 

One use can be to create an array of subtotals for use in an array formula or lookup formula where, for whatever reason, you don't/can't have a subtotal helper column. So, if you had numbers in A1:A10, and entered this in B1:B10, you would get a subtotal array (credit to Harlan Grove):

=MMULT(--(ROW(A1:A10)>=TRANSPOSE(ROW(A1:A10))),A1:A10)

I'm not an expert on it's use, but I've used it in situations where I needed an OR criteria in an array calculation, but it had to be condensed into a single dimension array (instead of leaving it 2D and multiplying it out-sometimes the other parts of the formula were also OR criteria with different dimensions and the normal array multiplication wouldn't work and MMULT was more compact than the usual array addition I would use for OR). 

Here's an example file of using mmult on a 2D data table with multiple OR criteria in both dimensions. 

[#9]

@Detlef Lewin Thanky you Dear Lewin , I used your approach to solve my problem