Quite a tricky SUM question

How to.png

So I have a long list of numbers and letters that sometimes account for a different amount of values i.e. ?h?h could have a value of 1 or 6. So i would like to find a way that will allow me to assign different values to the data i have and then add them all together. I am not sure how to do this. So i could have an extra column that perhaps has an If ?h?h = a certain cell or IF ?s?s = certain cell. But i was wondering if there was an easier way to do this as that would be a lot of IFs. The pic probably explains better. Hope you can help.

Thanks,

Excel noob

Sorry guys,

Imagine the cells were names and i wanted to make every cell containing the name "Dave" = to 1 and every cell containing "Peter" = to 2 so that i could create a sum that would add them all up e.g.
Dave
Dave
Dave
Peter
Dave would = 6

But i have data like 2s2c and 4s3s. I want to assign the number to the letter part of the cell data which is why at the top of the pic i put a row with ?s?s as it doesn't matter about the number.

So there is a long list of data that looks like this
2s2h
2c2s
3s2s
4s2s
5s2s
6s2s

So, this data equates to a number e.g.
3s2s = 1 and
2s2h = 2
or
?s?s = 1 and
?s?h = 2

The problem i have is that what number they are, changes between spread sheets. The numbers in the cells don't mean much, it's the letters that change what number they equate to. So i wanted to have an input row at the top which that means
Any cell containing
two h's = 1 (?h?h)
Any cell containing
two s's = 1 (?s?s)
Any cell containing
A s and a h = 2 (?s?c)

Sorry if i am being really confusing. I used ? as i know when you search a spread sheet and put ?s?s it will come back with all the cell that have those terms with the ? being anything.

Helppp.xlsxThank you. I have attached a work book that i hope shows the situation a little clearer. I know i am probably not doing a great job explaining myself.

EDIT: Please click the link in text for sheet

Originally Posted by ChemistB

In C5 copied down;
=INDEX($D$2:$H$2, MATCH("?"&MID(B4,2,1)&"?"&MID(B4,4,1),$D$1:$H$1,0))
Does that work for you?

that's perfect thanks very much Chemist

Originally Posted by ChemistB

In C5 copied down;
=INDEX($D$2:$H$2, MATCH("?"&MID(B4,2,1)&"?"&MID(B4,4,1),$D$1:$H$1,0))
Does that work for you?

One final question. So at the moment it is looking at the letters i.e. ?s?s but is there a way to add a further condition based on the numbers so that if the numbers are the same (regardless of the letters) then it will always be 1.
So
2h2h =1
3h3s =1
Something like

=IF(Range is X?X?,1,INDEX($D$2:$H$2, MATCH("?"&MID(B4,2,1)&"?"&MID(B4,4,1),$D$1:$H$1,0))

Thanks guys, been so much help

lewgill

We are almost there! That seems to work but when copied down in to a blank cell it counts it as one instead of just N/A or 0 any ideas on how to stop this?