Formula to Summarize Data?

I couldn't come up with a great title for this issue. Attaching a file.

What this is, is an ownership matrix. Each column labelled A-U represents a location, and cells are either greyed out, or follow a "XX/YY" format. These XXs and YYs represent employees responsible for the account in that particular location. Columns AA-AF attempt to summarize responsibilities by person. Therefore, what I'd hope to see would be a "1" in AA3 and also a "1" in AF3.

Is this possible?

if you see AA3 for example, you say you want a 1 to appear, even though MR appears in more than one location with more than one account number.

Do you just want to show those who DONT appear in the matrix?

Try:

=IF(ISNUMBER(MATCH("*"&AA$2&"*",$C3:$W3,0)),1,"")

copied across and down

in particular this section for me!! cheers N

("*"&AA$2&"*",

=IF(ISNUMBER(MATCH("*"&AA$2&"*",$C3:$W3,0)),1,"")

The MATCH(lookup_value,lookup_array,match_type) function looks for the lookup_value in the lookup_array. I used wildcards * since the lookup array had multiple items in each cell, so I am looking for the value that is in AA2 within any of the cells in C3:W3. If it finds one, it returns the position within the lookup array that it is found, if not found it returns #N/A error... the match_type of 0 means look for an exact match..

The ISNUMBER(value) function simply checks if the MATCH() function returned a position number or an error... and the IF(condition,value_if_true,value_if_false) says that if the ISNUMBER() function returns TRUE, then return a 1, else return nothing.

The $ signs "freeze" columns or rows (whatever follows the $) so that formula can be copied down and the referencing remains absolute.

Hope that helps.

Blake the * is a wildcard. So I am looking for something that "contains" the value found in AA2. Note the wildcard can only be used with certain functions, mainly the ones that have a comma separating the arguments to compare... e.g. MATCH, COUNTIF, SUMIF, SEARCH, FIND, etc... if you have an = separating the arguments, then the wildcard doesn't work, as in IF("x"="y") or Sumproduct(--(Array="X")), etc.

Makes perfect sense - thanks for your time. Cheers N

Try:

=COUNTIF($C3:$W3,"*"&AA$2&"*")

copied across and down.

You can hide 0's by formatting these cells as Custom: 0;-0;;@