# Excel - COUNT Function Problem - Expert Solution

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 E. S. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 25/05/2018 - 04:23
Hello!
Excelchat Expert 25/05/2018 - 04:23
Hello
Excelchat Expert 25/05/2018 - 04:23
Welcome to got it pro.
Excelchat Expert 25/05/2018 - 04:24
Can you please show me the file?
Excelchat Expert 25/05/2018 - 04:24
I need to see data to understand the problem clearly.
User 25/05/2018 - 04:25
Thanks! I am stuck on this issue from last few days. I would be really grateful if you can provide me the working solution
Excelchat Expert 25/05/2018 - 04:25
I believe I can help.
User 25/05/2018 - 04:26
Doc file for better and clear understanding (Just read page 1)
Excelchat Expert 25/05/2018 - 04:26
Although as I have mentioned earlier, I need to see the fiel.
User 25/05/2018 - 04:26
for now read the page 1 only as thats the phase one
User 25/05/2018 - 04:26
User 25/05/2018 - 04:26
here is the excel file link:
User 25/05/2018 - 04:26
User 25/05/2018 - 04:26
Excelchat Expert 25/05/2018 - 04:27
Thanks.
User 25/05/2018 - 04:28
Welcome!
Excelchat Expert 25/05/2018 - 04:31
So, I'm looking at the file and it seems I need more info to understand the problem.
User 25/05/2018 - 04:31
Excelchat Expert 25/05/2018 - 04:31
Can you please tell me what should be the result in AI8?
User 25/05/2018 - 04:32
let me explain you please open the excel file
Excelchat Expert 25/05/2018 - 04:33
I have it opened.
User 25/05/2018 - 04:34
Excelchat Expert 25/05/2018 - 04:34
Hmm.
User 25/05/2018 - 04:34
you will see its written: SHOOPEE 3W->3W
Excelchat Expert 25/05/2018 - 04:35
Yeah, I can see that.
User 25/05/2018 - 04:36
that the first criteria that we need to look for and match in the sheet
User 25/05/2018 - 04:37
we need to look for 2 times 3w side by side in the cell range B to H
User 25/05/2018 - 04:38
are you clear till now?
Excelchat Expert 25/05/2018 - 04:38
all clear except I don't understand the meaning of 2 times side by side
User 25/05/2018 - 04:39
sorry the range is B to I
User 25/05/2018 - 04:39
ok look H62 and I62 cells
Excelchat Expert 25/05/2018 - 04:40
I'm there.
User 25/05/2018 - 04:40
you will see there are 3w in both the cells, so 2 times 3w was repeated
User 25/05/2018 - 04:41
as asked in cell AI1 cell
User 25/05/2018 - 04:41
got it?
Excelchat Expert 25/05/2018 - 04:41
Okay, got it. Now going back to AI8, what should be result?
User 25/05/2018 - 04:42
yes, we will get the result after matching the second criteria. Now keep this in mind and lets move to next stage
Excelchat Expert 25/05/2018 - 04:42
listening....
User 25/05/2018 - 04:43
ok below cell AI1, you will find "1 / L+1W+2W" is written in cell AI2..that our next criteria
Excelchat Expert 25/05/2018 - 04:43
listening...
User 25/05/2018 - 04:44
Like in SHOOPEE 3W->3W : 3w->3w means 3wins...w=win and L=Lose
Excelchat Expert 25/05/2018 - 04:45
okay.
User 25/05/2018 - 04:45
1 / L+1W+2W = 1/ is shooter ID
User 25/05/2018 - 04:45
from cell range J to AH, you will find shooter1 to shooter25
User 25/05/2018 - 04:46
but in this particular column AI, we will collect the data for shooter1 only
Excelchat Expert 25/05/2018 - 04:47
May I know why so?
User 25/05/2018 - 04:48
so shooter1 in column J / either look for L or 1w or 2w (this 3 value) after 3w 3w (first creteria is match) look for this criteria in column J
User 25/05/2018 - 04:49
sorry "May I know why so?"
User 25/05/2018 - 04:49
Excelchat Expert 25/05/2018 - 04:49
In this particular column AI .....
Excelchat Expert 25/05/2018 - 04:49
that was my query.
User 25/05/2018 - 04:50
listening...
User 25/05/2018 - 04:50
AI is assigned for shooter1 only
Excelchat Expert 25/05/2018 - 04:50
that kind of answers my question.
User 25/05/2018 - 04:51
like if you look in column AK
User 25/05/2018 - 04:51
cell AK2 you will find its written 2/ that's shows that here we will match the data of shooter numer 2
Excelchat Expert 25/05/2018 - 04:52
Got it.
Excelchat Expert 25/05/2018 - 04:54
Next?
User 25/05/2018 - 04:54
ok now combine both the criteria match 3w-3w in column range B to I once matched look whether shooter1 (column J) have got any of the mentioned 3 values ( L or 1w or 2w)
User 25/05/2018 - 04:57
like all this is matched in cell H62 (3w), I62 (3w) and shooter1 (look in column J straight line) cells J62 (1w is written) one of the 3 value is there
Excelchat Expert 25/05/2018 - 05:00
Hmm, I see that now.
User 25/05/2018 - 05:01
so we got 1 our match - that will make AI62 cell answer= 1 (after applying the formula). one will be there only if you find 1st criteria + any of those 3 values for shooter1. for instance, if suppose you have found 3w or anything above 3w like 4w, 5w etc the it should reflect the value -7 in cell AI62
Excelchat Expert 25/05/2018 - 05:02
I do understand the result should be 1 when all criteria matched.
Excelchat Expert 25/05/2018 - 05:02
I need clarification on why -7 if the value is above 3W.
User 25/05/2018 - 05:02
yes, but if only 1st is matched and 2 not then -7
User 25/05/2018 - 05:03
*2nd not
Excelchat Expert 25/05/2018 - 05:03
Okay, Understood.
User 25/05/2018 - 05:03
if nothing match keep the cell blank
User 25/05/2018 - 05:03
got it?
Excelchat Expert 25/05/2018 - 05:03
Yeah, clear as crystal.
User 25/05/2018 - 05:03
great :)
Excelchat Expert 25/05/2018 - 05:04
The only fear now is that we might run out of time. Session maximum time is 1 hour and we are in the last 20 minutes.
Excelchat Expert 25/05/2018 - 05:04
I'm trying my best to finish it in time, please stay with me.
User 25/05/2018 - 05:05
I have taken lots of your time but hoping that we will get the result and you will free me from this frustration of not knowing the solution
User 25/05/2018 - 05:05
can't we extend the chat session
User 25/05/2018 - 05:05
Excelchat Expert 25/05/2018 - 05:05
Unfortunately we have used the 2 available extensions.
Excelchat Expert 25/05/2018 - 05:06
Thanks, in case we run out of time, can you please post again? I will keep working on the problem.
User 25/05/2018 - 05:07
yes sure. just tell me how this issue will be directed to your pot only and not assigned to any other expert avaible
Excelchat Expert 25/05/2018 - 05:08
I will bid minimum credits to claim the question. Since my rating is high and also I would bid low, the winning chance is 100%.
Excelchat Expert 25/05/2018 - 05:10
Can we use two helper row at the top of the sheet?
User 25/05/2018 - 05:10
I don't know what you will do on the back end or how your system works. But I don't want to repeat all this from scratch to any other advisor
Excelchat Expert 25/05/2018 - 05:10
No worries, I will finish the work.
User 25/05/2018 - 05:10
yes sure, you can add any row. we can hide that afterward, right?
Excelchat Expert 25/05/2018 - 05:11
Yeah, this would make the task a lot easier.
User 25/05/2018 - 05:11
great
User 25/05/2018 - 05:13
ok when I should re-post this query right away after this session or should I wait for few mints and post. and may I know your name I will write your name at the top of my query description
Excelchat Expert 25/05/2018 - 05:13
It would be great if you post a few minutes later, so I would get enough time to finish the work.
User 25/05/2018 - 05:14
hope that will help to identify which query is mine... I don't know how you will track my query :)
Excelchat Expert 25/05/2018 - 05:14
And unfortunately sharing personal identification information is against the rule of got it.
Excelchat Expert 25/05/2018 - 05:15
Please use [REPOST] tag and the same problem description.
User 25/05/2018 - 05:15
ok cool :)
User 25/05/2018 - 05:15
thanks for undertsnding my concern
User 25/05/2018 - 05:16
ok I will re-post after 30 mints, is that fine?
Excelchat Expert 25/05/2018 - 05:16
I'm really sorry that it took almost 40 minutes to fully understand the problem.
Excelchat Expert 25/05/2018 - 05:16
Yeah, that would be ideal.
User 25/05/2018 - 05:17
that's no problem. I am stuck in this for last 4 days, so 40 mints is still reasionable if I got the solution and will provide you the best review for that
Excelchat Expert 25/05/2018 - 05:18
Thank you.
User 25/05/2018 - 05:18
here I should say sorry as your 40 mints were invested in 1 query only
Excelchat Expert 25/05/2018 - 05:19
No No, Its really my fault. I was slow to understand. :'(
Excelchat Expert 25/05/2018 - 05:19
And hence it took a lots of time.
User 25/05/2018 - 05:21
ok once this session end I will re-post it after 30 mints. Please be there... I am scared, feeling like playing a blind game...hoping that everything works and I get you only
User 25/05/2018 - 05:21
:)
Excelchat Expert 25/05/2018 - 05:22
User 25/05/2018 - 05:22
Welcome & thanks :)
User 25/05/2018 - 05:23

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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.