SUM(IF formula that disregards a + or - in the cell

[Sirod]

Hello,

I am looking for a formula that will count matches disregarding a + or – sign in the cell. Below is a sample of my data. There are 10 criteria that I want to count matches on. The first line is the standard against which all other rows of data will be compared. Some of the criteria ratings have a + or – with the rating. The + or – should be ignored when counting matches (i.e. 3=3+=3- = match). The current formula in L3 is =SUM(IF($B$2:$K$2=B3:K3,1,0)). This gives a result of 2 matches for ID #13. The number of matches should actually be 5 (I have highlighted the matches in yellow to illustrate).

How can I amend my formula to disregard the + and -?

Thanks.

 

[Fluff]

If your criteria will always be one character as shown, try

=SUM(IF($B$2:$K$2&""=LEFT(B3:K3),1,0))

 

[Sirod]

If your criteria will always be one character as shown, try

=SUM(IF($B$2:$K$2&""=LEFT(B3:K3),1,0))

Thank you, that worked.

I have a follow-up questions - we haven't done this yet, but if one day we did decide to combine how we display criteria 8 & 9, i.e. 3/B or 3/A using the above examples, how would I amend the formula? The matches would still be based upon the two criteria separately, but the rating could be combined into one cell.

Thanks.

 

[Fluff]

Don't combine them. Each cell should contain just one piece of information.

 

[Sirod]

Don't combine them. Each cell should contain just one piece of information.

Don't combine them. Each cell should contain just one piece of information.

I agree. However, it may be out of my hands. These two criteria are often discussed in tandem and referred to together, so I can foresee needing to be able to count matches this way one day. Should it come up, do you have a solution? Many thanks.

 

[Fluff]

Not of the top of my head, it would almost certainly need to be a complicated array formula of some type.

 

[Peter_SSs]

I have a follow-up questions - we haven't done this yet, but if one day we did decide to combine how we display criteria 8 & 9, i.e. 3/B or 3/A using the above examples, how would I amend the formula? The matches would still be based upon the two criteria separately, but the rating could be combined into one cell.

What version of Excel are you using?

If Excel 365..

• Do you have the SEQUENCE function?
• Do you have the LET function?

Can you confirm that all criteria to be compared are single characters, even if 2 or more are combined in a single cell like 3/B?