**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.*