How to use array constraints with the COUNTIFS Function

Formula Friday #10

Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.

COUNTIFS make it easy to count as many things as you want all at once

We’ve covered the basics of all COUNT functions in a previous post. So today we are going to take a closer look at the COUNTIFS function.

In the previous post we looked at how to count the number of colour options for each of the different products categories. Today we’re going to build on that by introducing something called an array constraint.

First off let’s remind ourselves about COUNTIFS

The COUNTIFS function in Excel returns the count of cells that meet a set of specified criteria. To go back to our previous example we wanted to see the different colour options for each category.

COUNTIFs Example

There were so many different colourways. The likelihood is you wouldn’t be able to include all of these in a summary to present to management. They would be more interested in seeing a summarised view of different colour groups.

You could insert an additional column to be the colour group reference column. Or you could adapt the COUNTIFS formula you already have to include OR logic.

The default logic of the COUNTIFS function is AND. Which means you can string together different criteria and it will assume all of these need to be fulfilled before an answer can be given.

Let’s go back to our previous example for a moment, which used this formula

COUNTIFS(LD!$J$11:$J$817,Sheet1!C$30,LD!$N$11:$N$817,Sheet1!$B31)

This formula is asking for the number of instances where column J, on the LD tab, is equal to“Sng: Plain B” AND where column N, on the LD tab, is equal to “BLACK”.

Therefore, we can assume the first thing the formula does is check column J on the LD tab to count all the instances of “Sng: Plain B”. Then count the number of instances of “BLACK” in column N on the LD tab. And only return the number of instances where the two criteria show up at the same time.

Refresh over let’s getting to something shiny and new

Array constraints add in an additional layer of OR logic to our COUNTIFS. Instead of simply saying count this and that. We are saying count this or this or this AND that.

So if we wanted to just show the count for our core colours for each category we would use a formula like this.

=SUM(COUNTIFS(LD!$J$11:$J$817,Sheet1!C$30,LD!$N$11:$N$817,{“BLACK”,“WHITE”,“*BLUE*”}))

By adding the SUM function we are telling Excel to add together all the instances where our category “Sng: Plain B”, matches with the colour black or white or any colour that contains blue.

By using the asterisks around blue we have indicated that we want to include any colour option that contains the word blue.

There is a really helpful table from excel jet which shows the breakdown of how wildcard characters can be used in formulas. So if you want to find out a little more about that definitely check it out.

The same principles can be applied to numbers as well

Let’s say you need to pull together a summary of how many options there are at entry mid and exit price points, like in the below example

The formula you would need will look like this.

=SUM(COUNTIFS(‘SS19’!$D:$D,‘PRICE SUMMARY’!B$2,‘SS19’!$AC:$AC,{99,109,119,129}))

Just to note, numbers do not have to be input with quotation marks. The formula will work with or without quotation marks around the numbers.

Final thoughts

I think the best thing about the combination of array constraints and the COUNTIFS function, is that you don’t have to add in additional reference columns to your data. You can just take the data you have, and let the formula do all the heavy lifting to group together the different criteria.

If you think you’ve got a good use case for this formula but need a little extra help, feel free to drop me a line.

Until next time,

Ax

One reply on “How to use array constraints with the COUNTIFS Function”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.