**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need a formula written into cells (AI8 to EK8) to identify patterns and then count specific data. sheet name: SHOPEE PATTERNS ONE ROW have data of different shooter (wins and losses from cells B to H) that I need identify. There were 2 rows (AI4:EK4 and AI2:EK2) that will determine which cells to count and which to keep blank after applying the formula

Solved by V. H. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

User
22/05/2018 - 06:27

hello!

Excelchat Expert
22/05/2018 - 06:28

hi boss

Excelchat Expert
22/05/2018 - 06:28

:)

User
22/05/2018 - 06:29

please read it

User
22/05/2018 - 06:29

I need a formula written into cells (AI8 to EK8) to identify patterns and then count specific data. sheet name: SHOPEE PATTERNS ONE ROW have data of different shooter (wins and losses from cells B to H) that I need identify. There were 2 rows (AI4:EK4 and AI2:EK2) that will determine which cells to count and which to keep blank after applying the formula. For instance in cell AI1 written "SHOOPEE 3W->3W" that means I am looking for 2 consecutive wins in cells range from B to H. AI2 determine the second criteria "1 / L+1W+2W" 1 here is to address shooter number one those (L+1W+2W) tells us what I am looking in the column listed under shooter number 1

Excelchat Expert
22/05/2018 - 06:29

Ok Dear

User
22/05/2018 - 06:29

here is the file link

User
22/05/2018 - 06:29

https://drive.google.com/file/d/14tOI0P2fTFVaKbAbnZJ4O-7PqV-ALSM3/view?usp=sharing

User
22/05/2018 - 06:34

there?

Excelchat Expert
22/05/2018 - 06:35

Yeah i am working

User
22/05/2018 - 06:37

hello!

User
22/05/2018 - 06:37

are you still there?

User
22/05/2018 - 06:38

ok are you clear what I am looking for?

Excelchat Expert
22/05/2018 - 06:38

Yes Dear

Excelchat Expert
22/05/2018 - 06:38

I am Doing for you :)

User
22/05/2018 - 06:38

Great :)

User
22/05/2018 - 06:43

you can refer to the doc file (page 1 only for 1st stage) to know what value I want after the formula is applied in cells AI8 to EK8

User
22/05/2018 - 06:43

https://docs.google.com/document/d/1fBWLYxGAECcTn5AsOoYBVrbSKq1ZaA9Sht6ZNANnUfI/edit?usp=sharing

User
22/05/2018 - 06:54

any progress ?

User
22/05/2018 - 06:54

time is running out

Excelchat Expert
22/05/2018 - 06:54

yes i understand

Excelchat Expert
22/05/2018 - 06:55

sorry for being late now i am starting

User
22/05/2018 - 06:55

ok

Excelchat Expert
22/05/2018 - 07:03

[Uploaded an Excel file]

Excelchat Expert
22/05/2018 - 07:04

[Uploaded an Excel file]

User
22/05/2018 - 07:05

which one to downlaod

Excelchat Expert
22/05/2018 - 07:05

In last sheet i filled some cells could you please tell me is this ok

Excelchat Expert
22/05/2018 - 07:05

last one

User
22/05/2018 - 07:05

ok let me check

Excelchat Expert
22/05/2018 - 07:07

col al8:al18

User
22/05/2018 - 07:10

Sorry, its not what I wanted. Can you please refer to doc file that I have shared. that will give you better idea

User
22/05/2018 - 07:10

or I can tell you what i am exactly looking for

Excelchat Expert
22/05/2018 - 07:10

yeah sure

User
22/05/2018 - 07:11

go to cell AI1

Excelchat Expert
22/05/2018 - 07:11

now

User
22/05/2018 - 07:12

3W->3W , meaning I am looking for 2 times wins in a row

Excelchat Expert
22/05/2018 - 07:12

tell me range area

Excelchat Expert
22/05/2018 - 07:13

2 time wins mean 2W or like 2W + 2W

Excelchat Expert
22/05/2018 - 07:13

or 3W + 3W

User
22/05/2018 - 07:14

like check cell H77 and I77

Excelchat Expert
22/05/2018 - 07:14

oh ok

Excelchat Expert
22/05/2018 - 07:15

what is meant by

Excelchat Expert
22/05/2018 - 07:15

numbering giving before w

Excelchat Expert
22/05/2018 - 07:15

like 3 2 4 1

User
22/05/2018 - 07:15

you will find 3W 3W written side by side that means this cell meets the creteria for the search

Excelchat Expert
22/05/2018 - 07:15

ok

User
22/05/2018 - 07:16

suppose in shooting match he won 3 times and again 3 times in a row. Am I making any sense

User
22/05/2018 - 07:16

?

Excelchat Expert
22/05/2018 - 07:16

yes :)

User
22/05/2018 - 07:18

now if you check AI 2 it written "1 / L+1W+2W". here 1 / is to identity which shooter. its shooter number 1 (Shooter1 column J)

Excelchat Expert
22/05/2018 - 07:18

ok

User
22/05/2018 - 07:20

so in J column first look for 2 times 3w 3 win in different cell then see if in the straight J cell any of this 3 values are there L+1w+2w

User
22/05/2018 - 07:21

like in H77 and I77, you will see J77 (shooter1) its written 2w

User
22/05/2018 - 07:21

so its match the looking criteria. so the value here AI77 should be 1

User
22/05/2018 - 07:22

but if suppose we got any different vaule in cell J77 like 3w or anything above 2w then it should be -7

Excelchat Expert
22/05/2018 - 07:23

ok

User
22/05/2018 - 07:23

clear?

User
22/05/2018 - 07:24

is it possible to acheive ?

User
22/05/2018 - 07:24

* acheive

User
22/05/2018 - 07:25

there?

Excelchat Expert
22/05/2018 - 07:25

yes

Excelchat Expert
22/05/2018 - 07:26

m showing you sample as you told me

Excelchat Expert
22/05/2018 - 07:26

just give me 3 to 4 mins

User
22/05/2018 - 07:26

time is running out

User
22/05/2018 - 07:26

what should I do?

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*