Count uniques with criteria

I would think you are looking for...

=COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,B2)

...but this does not match your expected outcome in column C.

Can you explain why you are getting your results? The last three rows all seem the same so why is it 1?

Maybe...

=SUM(IF(FREQUENCY(IF($A$2:$A$8=$A2,MATCH($B$2:$B$8,$B$2:$B$8,0)),ROW($B$2:$B$8)-MIN(ROW($B$2:$B$8))+1),1))

IMPORTANT

• This is an array formula
• Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
• If entered correctly, the formula will be enclosed in {brackets}
• Do not enter the {brackets} manually

You're very welcome. Good to hear and thanks for the feedback.

I don't know if there is a non-array alternative, but maybe one of these other formula guru's can offer a suggestion.

https://www.mrexcel.com/excel-tips/C...ormulas-excel/

Originally Posted by MnMCarta

Is this one of those formula's that can make a sheet drag? The calculating is super slow since I put it in there yet it DOES work which I'm super stoked about just hate it when I don't fully get what's it's doing... but I WILL

Yes, a CSE formula will drag a sheet down, especially depending on the number of cells to calculate.

If you are able to use a helper column this may take some of the "load" off the calculations. Column D is the "helper" column. It returns unique numbers that correspond with unique pairings of columns A and B. Then the final formula in C references that helper. Though they are both technically array formulas neither of these require CSE.

	A	B	C	D	E
1	Agent / Level	Agent/Template	Expected Outcome	helpter
2	ABC	AACAGT Template	2	1	In D2: =INDEX((A2=$A$2:$A$8)*MATCH($B$2:$B$8,$B$2:$B$8,0),ROWS(C$2:C2))
3	ABC	AACAGT Template	2	1	In C2: =SUM(--(FREQUENCY($D$2:$D$8,D2)>0))
4	ABC	AACAGT Trailer Request Template	2	3
5	ABC	AACAGT Template	2	1
6	DEF	DEFAGT Template	1	5
7	DEF	DEFAGT Template	1	5
8	DEF	DEFAGT Template	1	5

Does this help?

Here are three more formulas. They are also array that do not require CSE. I am skeptical that they will perform any faster than what Jeff has proposed. All you can do is test them.

The first 2 build ranges unique to ABC/DEF. I suspect the first one is the slowest of the 3.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

and this one concatenates the ranges to force unique pairings.

Formula:

Please Login or Register  to view this content.

Another non CSE.

Formula:

Please Login or Register  to view this content.

Yes, the construction using SUMPRODUCT with a reciprocal COUNTIF is fine for very small ranges, but testing shows it's incredibly slow relative to the FREQUENCY/MATCH construction over mid to large ranges.

Regards

What about

=SUMPRODUCT((A2=$A$2:$A$8)/SomeRange)

Where SomeRange refers to =COUNTIF($B$2:$B$8,$B$2:$B$8)

As countif is only using absolute ranges, calculation hierarchy should mean that it is only processed once per recalc as opposed to for each cell holding the formula.

Not sure how it would perform on larger ranges compared to the 2 column countifs method that I suggested.

Originally Posted by jason.b75

SomeRange refers to =COUNTIF($B$2:$B$8)

Refers to what? That doesn't look like a valid syntax!

Regards

Ok, thanks. But isn't this in any case just a one-off formula to count the number of unique entries within a range? Why would it need to be recalculated more than once?

Regards

Ah, yes. I hadn't read the question properly. In that case you may well be right.

Regards

@ jason
RE posts 12 and 16.

Originally Posted by jason.b75

A non-array alterantive with a helper column.
In C2

Formula:

Please Login or Register  to view this content.

In D2

Formula:

Please Login or Register  to view this content.

Then fill both down to the end of your data.

Originally Posted by jason.b75

What about

=SUMPRODUCT((A2=$A$2:$A$8)/SomeRange)

Where SomeRange refers to =COUNTIF($B$2:$B$8,$B$2:$B$8)

As countif is only using absolute ranges, calculation hierarchy should mean that it is only processed once per recalc as opposed to for each cell holding the formula.

Not sure how it would perform on larger ranges compared to the 2 column countifs method that I suggested.

Suppose one used this for the helper column D. Not sure how concatenating ranges would affect performance though.

Formula:

Please Login or Register  to view this content.

Really it is not simple as I thought
Array formula in D2

Please Login or Register  to view this content.

Please try at C2 press Ctrl+Shift+Enter and copy down

=SUM(--(FREQUENCY(IF(A2=$A$2:$A$99,MATCH($B$2:$B$99,$B$2:$B$99,)),ROW($A$1:$A$99))>0))

I add data at row 9 to demonstrate result, and check if I understand it right.

Withdrawn by FR.

Please see next post.

Actually I now recall reading that as well ... I think it was Tony Valko.

Edited Overlooked detail in formula.
Another without array or concatenation.

Formula:

Please Login or Register  to view this content.