COUNTIFS with unique values Excel

Question

I am trying to produce a count of the number of times different strings come up in an Excel table. An example table, currently in SHEET1, would be this:

I have another table in another spreadsheet where I want to indicate, for each letter on the left in Table 1, how many entries for "za", "zc" or "zd" come up on the right. However, I would only like to only consider one entry of each.

The end result, on row B of SHEET2, would have to be something like this:

At the moment I am using a combination of SUM and COUNTIFS to do the job. More specifically, applied to the example, I am using the following formula:

=SUM(COUNTIFS(Sheet1!A1:A18,Sheet2!$A1,Sheet1!B1:B18,{"za","zc","zd"}))

The formula is doing some of what is intended. However, it is not counting each entry just one time. Instead, its is counting, for each letter on the left, every entry of "za","zc" or "zd". The table that the formula is returning is as follows:

How can I change the formula so that it does what I intend?

Thank you.

Erin Halbmaier Erin Halbmaier · Accepted Answer · 2017-07-20T22:53:22

My initial thought would be:

 =SUM(MIN (1,COUNTIFS(Sheet1!A1:A18,Sheet2!$A1,Sheet1!B1:B18,{"za","zc","zd"}))

but I’m not where I can test if the MIN will apply properly to the COUNTIFS array of results. ;-)

EDITED: The MIN function is taking minimum of 1 or all of the items in the COUNTIFS array, rather than minimum of 1 and each item in the COUNTIFS array, which is what I was afraid of. Using

=MIN(COUNTIFS(Sheet1!A$1:A$18,Sheet2!$A1,Sheet1!B$1:B$18,"za"),1)+MIN(COUNTIFS(Sheet1!A$1:A$18,Sheet2!$A1,Sheet1!B$1:B$18,"zc"),1)+MIN(COUNTIFS(Sheet1!A$1:A$18,Sheet2!$A1,Sheet1!B$1:B$18,"zd"),1)

will gain the desired results. It is a little clunky, but simpler than an array formula. If you want an array formula, you can use:

=SUM(FREQUENCY(IFERROR(MATCH({"za","zc","zd"},(IF(Sheet1!$A$1:$A$18=$A5,Sheet1!$B$1:$B$18)),0),""),IFERROR(MATCH({"za","zc","zd"},(IF(Sheet1!$A$1:$A$18=$A5,Sheet1!$B$1:$B$18)),0),"")))

This uses the FREQUENCY function to take a set of values and see how many items in another set of values fall within each of the data ranges. Since you need text instead of numbers, we use the MATCH function to find out the first time the value occurs in your list, returning "" with the IFERROR function if it doesn't. (We only need the first occurrence since you don't want to know how many occurrences there are). Since it is text, we use the same input for both arguments for FREQUENCY.

Therefore, if you need to change the values you are looking for or the ranges in which you are searching, make sure to change both! Alternately, you could list the values out somewhere, say in F1:F3, and make a named range for this, another one for A1:A18, and another for B1:B18. Your formula would then look something like this:

=SUM(FREQUENCY(IFERROR(MATCH(SearchValues,(IF(colA=$A2,colB)),0),""),IFERROR(MATCH(SearchValues,(IF(colA=$A2,colB)),0),"")))

Then you need only change your named range definitions and your formulas would update. :-)

NOTE: Since this is an array formula, you must close out of the cell by pressing CTRL+SHIFT+ENTER rather than only ENTER. When you look at the formula bar, you should see

{=SUM(FREQUENCY(IFERROR(MATCH(SearchValues,(IF(colA=$A2,colB)),0),""),IFERROR(MATCH(SearchValues,(IF(colA=$A2,colB)),0),"")))}

It does NOT work to enter the curly braces yourself. ;-)

COUNTIFS with unique values Excel

2 Answers