Quite a tricky SUM question

**Fotis1991** · 05-02-2013, 06:49 AM

Originally Posted by :) Sixthsense :)

I am unable to understand your requirement from the picture you uploaded

+1 to this.

**lewgill** · 05-02-2013, 07:34 AM

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.

**:) Sixthsense :)** · 05-02-2013, 07:38 AM

Thanks for the brief and you will get the answer in seconds if you attach a sample workbook with expected output. Because your attached image doesn't cover the row/column references.

**lewgill** · 05-02-2013, 08:01 AM

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