SOLVED

Highlighted
New Contributor

# Excel counting

Dear excel community and @Faraz Shaikh (thank you very much for helping with previous topic),

could you please help with next question:

For example, I have column of:

where each 2 is "LS"

and

0 is "5.5 1BL" or "5.5 2BL" (alternating with each other)

I would like to have output from formula:

5.5 1BL 1
5.5 LS 1
5.5 2BL 1
5.5 1BL 2
5.5 LS 2
5.5 LS 3
5.5 LS 4
5.5 2BL 2
5.5 1BL 3
5.5 LS 5

Thank you in advance!

Regards,

Denis

2 Replies
Highlighted
Best Response confirmed by DShamanov (New Contributor)
Solution

# Re: Excel counting

C1

``="5.5 "&IF(A1=2,"LS",CHOOSE(MOD(COUNTIFS(A\$1:A1,A1),2)+1,"2BL","1BL"))``

D1

``=COUNTIFS(C\$1:C1,C1)``

Highlighted

# Re: Excel counting

Hi @DShamanov,

Find below solution for your query.

You need to construct your formula using helper columns that will give you desired results.

First helper1 [Column B] formula

``=IF(A2=2,"5.5 LS","5.5 1BL")``

Second helper2 [Column C] formula

``=IF(RIGHT(B2,2)="LS",B2,IF(MOD(COUNTIF(\$B\$2:B2,B2),2)=1,B2,"5.5 2BL"))``

Result [Column D] Formula

``=C2&" "&COUNTIF(\$C\$2:C2,C2)``

Output

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more