< Go Back

Sum if cells contain an asterisk

Sum if Cell contains an Asterisk

The SUMIF function in Excel is used with a special character of “~” in order to sum the cells with an asterisk “*”. The example of the SUMIF function for calculating cells with an asterisk is as follows:

Figure 1: SUMIF Function to calculate cells with an asterisk


The basic formulation of the syntax along with the use of “~” is:

=SUMIF (range1, “*~*”, range2)

The parameters of this formula in accordance with the example can be:

Range1 – it is the range of cells that need to be tested with the criteria

Criteria (“*~*”) – it is the criteria of summing the cells that contain an asterisk, composed of special character “~”

Range2 – The actual sum range needs to be summed against the criteria


In accordance with the figure above the example that has been derived is as follows:


The SUMIF function is being used here just like a normal sum function but here the use of the special character “~” along with an asterisk has specified that only those cells that have an asterisk shall be added. So the function will take into consideration only those cells that have an asterisk ( C7, C9, C11) and will return the values that are in the actual sum range category which in this case are ( D7, D9, D11) and display the summed result that is $50.00 respectively.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment