# How to count different charecters

Solved/Closed
mathewmunna Posts 27 Registration date Tuesday November 18, 2014 Status Member Last seen May 21, 2015 - Nov 26, 2014 at 08:37 AM
mathewmunna Posts 27 Registration date Tuesday November 18, 2014 Status Member Last seen May 21, 2015 - Dec 4, 2014 at 03:37 AM
Hello,

Please help me to count different charecters like xpa, spa, spb xpb.. if any of this exist in the cell it should count. if xpa is in A1 spa is in A2, it shoud be 2.. and also want to multiply the result by 2. so output should be A3=4..

## 2 responses

Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Nov 26, 2014 at 10:35 AM
mathewmunna, Good afternoon.

Will these groups of characters appears in a text at A1; A2 or they will appears isolated as xpa or spa etc at a cell?

Give us an example how they will appears.
mathewmunna Posts 27 Registration date Tuesday November 18, 2014 Status Member Last seen May 21, 2015
Nov 27, 2014 at 12:11 AM

assume i have to count xpa or xpb or spa or spb in 2 cells i e A1 & A2 no other texts i e if the cell contains xpc, xpd .... it should not count

eg:

A1=XPA
A2=XPB
RESULT A3=(1+1)*2

A1=XPB
A2=SPB
RESULT A3=(1+1)*2

A1=SPA
A2=XPC
RESULT A3=(1+0)*2

A1=SPD
A2=SPC
RESULT A3=(0+0)*2
mathewmunna Posts 27 Registration date Tuesday November 18, 2014 Status Member Last seen May 21, 2015 > mathewmunna Posts 27 Registration date Tuesday November 18, 2014 Status Member Last seen May 21, 2015
Nov 27, 2014 at 08:23 AM
I got a formula but very long..
=IF(A1="XPA",2,IF(A1="XPB",2,IF(A1="SPA",2,IF(A1="SPB",2,))))+IF(A2="XPA",2,IF(A2="XPB",2,IF(A2="SPA",2,IF(A2="SPB",2,))))
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Nov 28, 2014 at 06:10 AM
mathewmunna, Good morning.

Scenario:
A1 = XPA
A2 = SPB
---------------
L1 = XPA
L2 = XPB
L3 = SPA
L4 = SPB
---------------
Try to use:
A3 -->
=SUM(IFERROR(MATCH(A1,L1:L4,0),0);IFERROR(MATCH(A2,L1:L4,0),0))*2

Is this what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
mathewmunna Posts 27 Registration date Tuesday November 18, 2014 Status Member Last seen May 21, 2015
Dec 4, 2014 at 03:37 AM
Yes, It does Work.. But it would be better if it can check in the same cell instead of L1:L4..

i am looking for if ((B79=(SP1 or SP2 or......SP8) + B80=(SP1 or SP2 or......SP8)), multiply it with 2

Scenario:
B79 = SP1
B80 = SP8
---------------
RESULT (1 + 1) *2

Scenario:
B79 = SP1
B80 = P10
---------------
RESULT (1 + 0) *2

Scenario:
B79 = P9
B80 = P10
---------------
RESULT (0+ 0) *2

i am using exact this formula to confirm SP1 TO SP8 (i.e signal pole numbers) (other poles are named like P9, P10...)

=IF(B79="SP1",2,IF(B79="SP2",2,IF(B79="SP3",2,IF(B79="SP4",2,IF(B79="SP5",2,IF(B79="SP6",2,IF(B79="SP7",2,IF(B79="SP8",2,0))))))))+IF(B80="SP1",2,IF(B80="SP2",2,IF(B80="SP3",2,IF(B80="SP4",2,IF(B80="SP5",2,IF(B80="SP6",2,IF(B80="SP7",2,IF(B80="SP8",2,0))))))))