Why should softmax be used in CNN
Yes, you are right. The soft-max layer outputs a probability distribution, i.e. the values of the output sum to 1. The sigmoid function outputs marginal probabilities and therefore can be used for multiple-class classification, when the classes are not mutually exclusive. Additionally the soft-max layer is soft version of the max-output layer so it is differentiable and also resilient to outliers. A problem with sigmoids is that as you reach saturation (values get close to 1 or 0), the gradients vanish. This is detrimental to optimization speed and soft-max doesn't have this problem. Another interpretation is soft-max as a generalization of sigmoid, actually when there are two classes they are the same.

Wrapping up you should use soft-max when the classes are mutually exclusive and sigmoid when the classes are independent. This can be summarized in the following table:

Soft-Max                            |                 Sigmoid
-------------------------------------------------------------------------
Used for multi-classification       |  Used for binary classification in 
in logistic regression model.       |  logistic regression model.
-------------------------------------------------------------------------
The probabilities sum will be 1     | The probabilities sum need not be 1
-------------------------------------------------------------------------
Used in the different layers of     | Used as activation function while 
neural networks.                    | building neural networks
-------------------------------------------------------------------------
The high value will have the higher | The high value will have the high 
probability than other values.      | probability but not the higher


For more information on soft-max look at the following links: 1, 2 and 3. For a step-by-step guide, including usage in Python see this reference 4. 

For more on soft-max vs sigmoid check this: 5, 6 and this 7. 

If you want a more reliable source on why to use soft-max regression for mutually exclusive classes you can look here. This page is part of Unsupervised Feature Learning and Deep Learning Tutorial of Stanford University, it contains material contributed by Andrew Ng and others.