How to count different charecters [Solved/Closed]

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

Posts
1834
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 24, 2020
135
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
27
Registration date
Tuesday November 18, 2014
Status
Member
Last seen
May 21, 2015

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

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,))))
Posts
1834
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 24, 2020
135
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
27
Registration date
Tuesday November 18, 2014
Status
Member
Last seen
May 21, 2015

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))))))))