Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

In my spreadsheet I have a list of people. In column A I have got their name, in column B the society they work for, in column C how many times they held a positionX, in column D how many times they held a positionY, in column E how many times they held a positionZ. Not all of them held a position X, Y or Z. They could have held one of them, two of them, all of them or none. I need to know how many of them held a position (no matter which one, how many times or how many of them), divided by society. Is that possible?
Solved by I. L. in 12 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 01/03/2018 - 04:45
Hello. Welcome to Excel chat.
User 01/03/2018 - 04:45
User 01/03/2018 - 04:46
My spreadsheet looks like this
Excelchat Expert 01/03/2018 - 04:46
Good to see you working on the sample data. That will help in finding a solution.
Excelchat Expert 01/03/2018 - 04:47
Ok. I have understood how the data looks like. So what is your expected outcome.
User 01/03/2018 - 04:48
In this case, I should get a 6
Excelchat Expert 01/03/2018 - 04:48
For ?
User 01/03/2018 - 04:48
Because all of them 6 held a position, no matter which one nor how many times
Excelchat Expert 01/03/2018 - 04:49
Oh Ok. Got it.
User 01/03/2018 - 04:49
Even with the last addition, it still is 6
Excelchat Expert 01/03/2018 - 04:49
So if some one has held no position then, it will ignore that value.
User 01/03/2018 - 04:49
Excelchat Expert 01/03/2018 - 04:51
The sum of the column F is your answer.
User 01/03/2018 - 04:52
Thank you very much!
Excelchat Expert 01/03/2018 - 04:52
I have entered a formula in that, which will give 0 if all the columns C, D and E are blank, and 1 if either of them is filled.
Excelchat Expert 01/03/2018 - 04:52
If you are satisfied with the solution, I am happy to explain it for you.
User 01/03/2018 - 04:52
I really am
Excelchat Expert 01/03/2018 - 04:52
Great! Happy to help you.
User 01/03/2018 - 04:53
You've been really helpful, thank you
Excelchat Expert 01/03/2018 - 04:53
I have used an AND function inside an IF. The AND function will result TRUE when all the conditions in it are TRUE.
Excelchat Expert 01/03/2018 - 04:54
The condition I am checking is if the cell is blank by using ISBLANK function.
User 01/03/2018 - 04:54
It looks great
Excelchat Expert 01/03/2018 - 04:54
Now if my AND condition returns TRUE, then it means that all the 3 columns are blank hence contains no value for us i.e 0
Excelchat Expert 01/03/2018 - 04:55
And if AND function returns false, then atleast 1 column has been used and hence 1 is used. And our final result is the sum of the column where this formula is applied.
Excelchat Expert 01/03/2018 - 04:56
I hope you will visit us again soon.
User 01/03/2018 - 04:56
I surely will
User 01/03/2018 - 04:57
I hope I can leave a positive review somewhere
Excelchat Expert 01/03/2018 - 04:57
You can end this session now and please don't forget to rate your experience.
User 01/03/2018 - 04:57
I want to thank you again for your time and your help. See you soon
Excelchat Expert 01/03/2018 - 04:57
Have a nice day ahead!
User 01/03/2018 - 04:57
You too!
Excelchat Expert 01/03/2018 - 04:57
:) Bye!

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
The Allstate Corporation
United Parcel Service
Dell Inc