Conditional COUNT in Excel: Count cells meeting multiple criteria + Data Cleanup Tip

We have been provided with a top secret data of one lab situated at the very center of the earth. A team of vigilant individuals take readings of different parameters to monitor the temperature of facility so that it is kept in the range good for human life.

Now we want to count the number of tests done in each month for each parameter.

Bird’s-eye view ALWAYS!

Yes! And I mean it. Most of the time problem is not what your client is stating. Most often it is somewhere else but not known to client.
I was lucky that I went by the rule. And saw that mostly are figures but some figures had (rechecked) appended to them. This means not all the figures in the data are actually numbers, rather some of them will be text.

Same was the case with the dates. Some were not written in a proper format and thus were not dates in the eyes of Excel. And if something is not as Excel wish it to be, we cannot use it effectively.

Correcting these two issues is an added task to our original assignment of doing conditional count. Therefore, first I will clean up the data and then I will proceed with the actual task.

Conditional formatting to detect wrong data values

Conditional formatting is one cool feature. It has just no bounds the way it can be put to use. We can use conditional formatting to highlight cells that are not numbers or are text. I will apply conditional formatting with the condition “if it is text” then highlight the cell with specific color otherwise leave it as is. This way I will identify cells that needs correction so that my results are not distorted.

Correcting Dates

Step 1: Select the dates with mouse of keyboard

Step 2: Go to Home tab > Styles Group > Click conditional formatting drop down and select new rule.

Step 3: From the dialogue select the last option “Use a formula to deterine”

Step 4: Put this formula in the field:

=ISTEXT(B2)

Step 5: Click format button, Go to fill tab and select any color. The same color will be used to highlight text values. Click OK

Now you will be able to see which date is not formatted correctly. Give them a proper format so that if you apply a function on dates, they don’t give error.

Following animation walks you through all steps:

Correcting Tests data

Using the same technique that you used on dates, you can highlight the test data which is not in number format. The data shoud can either be an empty cell or a number not anything else. Brief inspection that I did in the beginning I noticed that some of the cells seemed to be empty but in reality they had some spaces entered. Some of the similar problems were also detected which I was able to correct once I highlighted all figures that were formatted as text when they should be in numbers.

Following animation helps you do the necessary in this case:

Named Ranges – Making things easy

Giving a range a specific name make things a lot easier. Even writing formulas. As Excel will remember the name, you do not have to jump between worksheets and places inside the same worksheet. I defined three names as follows:

On main register tab select all the dates, i.e. from cell B2 to B286. hit Ctrl+F3. Name manager will open. Click New button and. Give name as ‘date’ and click OK

Select the names of parameter at the top of data i.e. cell D1 to M1 and hit Ctrl+F3. Click New button and give a name ‘para’. Click OK

Select all the tests results i.e. from cell D2 to M286. Hit Ctrl+F3 shortcut. Click new button and name it as ‘tests’.

With these three names defined we can now easily write formula and we will not have to visit the worksheet everytime we insert a formula. We will only mention the name of range.

Counting the tests conducted each month for each parameter

Once numeric and dates data is corrected. Now we can count the results. Follow these steps:

Step 1: Go to ‘test stats’ tab. Here you have all the data formatted already by our inside man and he wants to count the tests against each parameter under each month.

Step 2: In cell E6 put this formula and press Enter key:

=SUMPRODUCT((--ISNUMBER(tests)*(--(para=$B6))*(--(TEXT(date,"mmm")=E$4))))

Step 3: Now first drag the handle accross the column until the column of August and then drag the handle down to the row of Para 10.
Done! Count is done 🙂

Understanding the formula:

To understand how SUMPRODUCT works please refer to this detailed article as it walks you through the basics of it.

The formula has three parts. Two of them are conditions and the other one is the data we want to count. Lets discuss them one by one:

1. para=$B6: On Test stats worksheet we have names of parameter in column B. We use each of these names in our formula to fetch only the relevant data. By equating the named range equal to cell basically we want only that data pertaining to parameter name mentioned in the cell reference. For example having para=$B6 means we want formula to fetch only “Para 1” data as in cell B6 “Para 1” is mentioned.
2. TEXT(date,”mmm”)=E$4: This formula is doing two jobs. First it is finding the month of each date in the ranged named “date”. Once the month is found it will format it is Jan, Feb, Mar and so on. Once this is done, second step is executed i.e. to fetch only that month which is same as mentioned in cell reference.
   For example having TEXT(date,”mmm”)=E$4 will first format all the dates in terms of month names expressed in three letter words i.e. Jan, Feb, Mar, Apr… and so. Then It will only select the month name which is same as mentioned in E$4, which is Jan, and disregard the others. This way only the data of January will be selected.
3. ISNUMBER(tests): This checks if the data in range named ‘tests’ is a number or not. If it is not a number (because it is either a text or a blank cell) then disregard and if it is a number then include in count. As we have already corrected our data and it does not contain text values, it will only have numbers and blank cells. This way only tests that are conducted will be counted and blank cells (that means no tests) will be excluded from count.

In short, we have a formula in words like this:

Count the values that meet following criteria:

• only numbers
• month name is same as mentioned in cell (e.g. E4)
• parameter name is same as mentioned in cell (e.g. B6)

So how do you solve similar problems? Have you heard about COUNTIF? and COUNTIFS? Well I leave this puzzle for next time to be discussed as these are yet another formula available for you to do conditional counts but they do have limitations. But still worth learning 🙂