This is my formula =IF(COUNTIF(B$6:B$21,"*"&$A6&"*"),"","Free"). Instead of showing nothing if CountIf returns 1, I'd like to show the value of the cell. Because it is a range, I'm not sure how to show just the value of the cell where it matches my condition. Kindly advise. Thanks a lot!
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
You're missing part of the IF
eg. if 1 = 1 then return 1 else o
IF(1=1,1,0)
Since COUNTIF returns a number you have something like =IF(1,1,0) you need 1 to equal something or be greater than or less than etc.
Maybe show some dummy data and what you want the result to be.
eg. if 1 = 1 then return 1 else o
IF(1=1,1,0)
Since COUNTIF returns a number you have something like =IF(1,1,0) you need 1 to equal something or be greater than or less than etc.
Maybe show some dummy data and what you want the result to be.
Upvote
0
Peter_SSs
MrExcel MVP, Moderator
- Joined
- May 28, 2005
- Messages
- 63,880
- Office Version
- 365
- Platform
- Windows
You don't really since Excel treats zero as a False condition and any other number as a True condition
@junniec
Welcome to the MrExcel board!
See how this goes.
=IFERROR(INDEX(B$6:B$21,MATCH("*"&$A6&"*",B$6:B$21,0)),"Free")
Upvote
0
Peter_SSs
MrExcel MVP, Moderator
- Joined
- May 28, 2005
- Messages
- 63,880
- Office Version
- 365
- Platform
- Windows
It seems that your data and/or layout is not realistic. If it was, all you need is this
What about some more realistic (varied) sample data and layout and expected results with XL2BB so we can see clearly where the data and results are and don't have to manually type out the data to test with?
| 20 08 07.xlsm | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 6 | Jane | English/.Jane | English/.Jane | Jane | Free | Free | Free | |||||||||
| 7 | Esther | Business/Esther | Business/Esther | Esther | Free | Free | Free | |||||||||
| 8 | Alvin | Maths/Alvin | Maths/Alvin | Alvin | Free | Free | Free | |||||||||
Free | ||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I6:I8 | I6 | =B6 |
| J6:N8 | J6 | =IF(C6="","Free","") |
What about some more realistic (varied) sample data and layout and expected results with XL2BB so we can see clearly where the data and results are and don't have to manually type out the data to test with?
Upvote
0
This seems to be working ! Thanks Peter!
Upvote
0
I'd love to upload the actual excel file too but it did not allow me to, sorry as this is my first post! Love the prompt support the team is giving here. I've got the solution from Peter for now, thanks again!It seems that your data and/or layout is not realistic. If it was, all you need is this
20 08 07.xlsm
A B C D E F G H I J K L M N 6 Jane English/.Jane English/.Jane Jane Free Free Free 7 Esther Business/Esther Business/Esther Esther Free Free Free 8 Alvin Maths/Alvin Maths/Alvin Alvin Free Free Free
Cell Formulas Range Formula I6:I8 I6 =B6 J6:N8 J6 =IF(C6="","Free","")
What about some more realistic (varied) sample data and layout and expected results with XL2BB so we can see clearly where the data and results are and don't have to manually type out the data to test with?
Upvote
0
Similar threads
