Question description:
This user has given permission to use the problem statement for this
blog.
in range b2:o2, I am trying to write a formula to count if the values are equal to 1 thru 27.
Solved by E. L. in 52 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
10/10/2018 - 03:51
Hello, I understand that you are trying to count values that are between 1 and 27 in the range b2:o2, right?
Excelchat Expert
10/10/2018 - 03:52
Are you there?
Excelchat Expert
10/10/2018 - 03:52
If you can read this but are unable to reply please try to refresh your browser.
Excelchat Expert
10/10/2018 - 03:52
Otherwise, you can try to send a message using the sheet to the right.
User
10/10/2018 - 03:52
yes I am here
Excelchat Expert
10/10/2018 - 03:53
Hello.
Excelchat Expert
10/10/2018 - 03:53
I understand that you are trying to count values that are between 1 and 27 in the range b2:o2, right?
User
10/10/2018 - 03:53
yes
Excelchat Expert
10/10/2018 - 03:53
Aright, I can help you with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
User
10/10/2018 - 03:53
ok
Excelchat Expert
10/10/2018 - 03:54
Will 1 be counted or will the count start from 2 to 26?
User
10/10/2018 - 03:54
1 will be counted. actually numbers are 1 thru 28.
Excelchat Expert
10/10/2018 - 03:55
I see. Thank you for the clarification.
Excelchat Expert
10/10/2018 - 03:55
I'll be working on this using the sheet to the right. We will be using the COUNTIFS formula for this.
User
10/10/2018 - 03:55
okay
Excelchat Expert
10/10/2018 - 03:56
Please see the formula in column A.
Excelchat Expert
10/10/2018 - 03:56
=COUNTIFS(B2:O2,">="&1,B2:O2,"<="&28)
Excelchat Expert
10/10/2018 - 03:56
This formula counts the number of values from 1 to 28 in
the range B2:O2.
Excelchat Expert
10/10/2018 - 03:57
=COUNTIFS(CRITERIA_RANGE1,CRITERIA1, CRITERIA_RANGE2,CRITERIA2)
Excelchat Expert
10/10/2018 - 03:58
So basically that's COUNTIFS(B2:O2,greater than or equal to 1, B2:O2,less than or equal to 28)
Excelchat Expert
10/10/2018 - 03:58
Got the formula?
User
10/10/2018 - 03:59
Yes, but I guess I didn't ask the question correctly.
Excelchat Expert
10/10/2018 - 03:59
Please restate your question. Let's see if we have enough time to solve it.
Excelchat Expert
10/10/2018 - 04:02
Are you still with me?
User
10/10/2018 - 04:02
b2 can have a value of 1 or 2, c2 can have a value of 3 or 4, d2 5 or 6, etc so if b2=2 & c2=3 & d2 = 6 the count should be 3
Excelchat Expert
10/10/2018 - 04:03
But the formula I gave you will result to that.
Excelchat Expert
10/10/2018 - 04:03
See the sheet to the right.
Excelchat Expert
10/10/2018 - 04:05
Are you there?
User
10/10/2018 - 04:05
yes, it will, but suppose the value in c2 is 4, your formula will still give me 3. the answer should ne only 2
User
10/10/2018 - 04:05
be not ne
Excelchat Expert
10/10/2018 - 04:06
I'll need to know why 4 won't be counted though. The range you gave me is 1 to 28 and 4 is very clearly inside that range.
User
10/10/2018 - 04:09
the value in each of those cells can only be one or the other, basically i need a formula to count the exact value of what is inside each cell, if b2=2 count it, if cell c2 =4, don't count it,etc
Excelchat Expert
10/10/2018 - 04:10
I'm not sure I understand. Excel will need to know the exact rules or else a formula cannot be written.
Excelchat Expert
10/10/2018 - 04:10
Why is 2 counted and why is 4 not counted. You will have to give the exact reason why.
User
10/10/2018 - 04:11
can i attach the spreadsheet i have
Excelchat Expert
10/10/2018 - 04:11
Yes please.
Excelchat Expert
10/10/2018 - 04:11
Please click the clip icon next to this chat and select your file to attach it. Then say something in chat to complete the transfer.
User
10/10/2018 - 04:11
can you see it
[Uploaded an Excel file]
Excelchat Expert
10/10/2018 - 04:12
I just opened it.
Excelchat Expert
10/10/2018 - 04:12
Your row 2 does not have any numbers though.
Excelchat Expert
10/10/2018 - 04:12
It says TBB @ ATL etc.
User
10/10/2018 - 04:15
right, TBB will be 1 & ATL will be 2, the value will depend on who wins. the top teams are the odd numbers 1-27, the bottom team are the even numbers, 2-28. so depending on who wins, i need to count the cell on the winning teams.
Excelchat Expert
10/10/2018 - 04:17
How will we know who won?
Excelchat Expert
10/10/2018 - 04:18
I think you have a concept in mind but are unable to put it into words so I think it would be best to simply give me an example that we can work on.
Excelchat Expert
10/10/2018 - 04:18
Can you send another file with examples and their respective results and then we'll work from there?
User
10/10/2018 - 04:18
The sheet is populated by the participants. they pick who they think will win. the formula will check for the winning team and count it as a win.
Excelchat Expert
10/10/2018 - 04:19
Then please provide a sample populated file otherwise there's no way for me to understand what you are thinking.
Excelchat Expert
10/10/2018 - 04:20
I'm starting to get an idea of what you need but before I can even commit to my answer, I'll need to be sure that I understand what you need.
User
10/10/2018 - 04:20
I can't right now, i thought this would be quick and unfortunately, i have a meeting to go to. thank you for your help.
Excelchat Expert
10/10/2018 - 04:20
So the users will be typing which team they think will win, right?
Excelchat Expert
10/10/2018 - 04:20
And if they chose correctly, they will be scored.
Excelchat Expert
10/10/2018 - 04:21
The problem with that requirement is nowhere in the sheet says who won.
Excelchat Expert
10/10/2018 - 04:22
What needs to be done here is to add a new row which will state who won between the matchups. Once we have that row, we can very easily count those who guessed correctly.
Excelchat Expert
10/10/2018 - 04:22
I'll show you what I meant. Please give me a few minutes.
Excelchat Expert
10/10/2018 - 04:26
I have the solution ready. Just note that this is very far from what was initially described in the session description so it requires a much more complicated formula instead of just the COUNTIFS formula.
Excelchat Expert
10/10/2018 - 04:27
1 to 27 isn't even considered here, but more of count if the winner prediction is correct.
Excelchat Expert
10/10/2018 - 04:27
And honestly even now, I'm still not sure if this is what you need simply because the initial description does not match what we've discussed at all.
Excelchat Expert
10/10/2018 - 04:29
Here's the file, I have to add ROW 3 in order for everything to make sense. Your file needs a row to specify who won because the sample file you gave me does not state that anywhere at all.
[Uploaded an Excel file]
Excelchat Expert
10/10/2018 - 04:30
I sincerely hope that this session will be rated fairly and that this session will be read properly by our auditors to ensure the accuracy of the ratings.
Excelchat Expert
10/10/2018 - 04:31
The formulas used are array formulas so they need to be confirmed by using CTRL+SHIFT+ENTER instead of just ENTER. You'll know that you've done it correct if the formula gets automatically enclosed in { }
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.
