Count Cells Not Equal to Many Things
Counting cells not equal to many things is not as difficult as many tend to think. For instance, you could count cells not equal to some cells containing other varied data or items. You may want to know how cells with data A are not equal to C D E F G.
=COUNTA (range)-SUMPRODUCT(COUNTIF(range, things))
How to Count Cells Not Equal to Many Things
Please note that the above is a generic formula that could be modeled after your excel spreadsheet. To count the cells not equal to many things, you would have to use a formula that contains elements of COUNTIF, SUMPRODUCT, and COUNTA. Do not allow this to confuse you. It seems like a pretty long formula. However, when inputted correctly, it makes the job of sorting out unequal data very easy and effortless. The importance of knowing how to count cells not equal to many cannot be overemphasized when you want to know data or cells that belong to the same category.
How the Formula Works
Knowing how the formula works would reduce the complex look of the stringy formula. For instance, the part name range which could (A2:E11) is used to point out the cells we are leaving out of the count. So to that, we begin by cells we want to be counted with COUNTA as in
=COUNTA(range or B2-C6).
Then we apply the part COUNTIF to eliminate we do not want to count as in COUNTIF(range or C2:C7). SUMPRODUCT, which is the next part of the formula is used to make a subtraction from the total result to get the exact cells that are not equal to the things we have specified in the formula.
So you want to ask why we SUM cannot be used instead of using SUMPRODUCT which is a bit longer. The reason for doing so is to avoid complicating the formula and making it longer.
Now, let’s see how we could use this formula on a spreadsheet modeled after the stocks of a small store.
Make sure you enter all the data correctly as shown below.
Figure 1. Data entry
Select the cell in front of the not things then enter your formula to see how this simply works.
Figure 2. Excel showing cells not equal to many things
It is as simple as it looks up there. The formula may look quite complex but the application is easy.
- Many try bypassing the product by interchanging SUMPRODUCT with SUM. Doing so would only complicate the formula.
- To avoid errors when entering the data, avoid muddling up your data on the spreadsheet. To do this, neatly separate your data in cells. You could divide the spreadsheet as seen above into 3 columns.
- When dividing the spreadsheet, always ensure the data you are working with are in view of your screen to avoid the situation where you have to scroll around to enter your cells numbers.
- Finally, please always ensure to close your sub-brackets as this is a common mistake many when trying out the formula for the first time.