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 replies

Mazzaropi
Posts
1965
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 18, 2022
146
Nov 26, 2014 at 10:35 AM
mathewmunna, Good afternoon.

Please, your doubt, for me, is not clear.

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.
1
mathewmunna
Posts
27
Registration date
Tuesday November 18, 2014
Status
Member
Last seen
May 21, 2015

Nov 27, 2014 at 12:11 AM
thanks for your response...

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
0
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,))))
0
Mazzaropi
Posts
1965
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 18, 2022
146
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
1
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))))))))
0