Excel - IF Function Problem - Expert Solution

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.

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.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc