Retrieve unique items with 2 criteria (USING SUMPRODUCT)
 

This question was posted before but the solution proposed used different
functions. I would like to insist. Is there a modification to the formula
below, using SUMPRODUCT, that allows to retrieve the number of unique itens
that meet a 2nd criteria?

Original formula:
=SUMPRODUCT((A1:A20<"")/COUNTIF(A1:A20,A1:A20&""))

In my case, I need the numbers of unique branches (column B) that meet a
specifc criteria in another column (M). So, I have the formula:

=SUMPRODUCT((all!$B$2:$B$5514<"")/COUNTIF(all!$B$2:$B$5514,all!$B$2:$B$5514&""))

but I need to include the 2nd criteria:

(all!$M$2:$M$5514=$A22)


Thanks in advance,

Paulo

Retrieve unique items with 2 criteria (USING SUMPRODUCT)
 

Are there any empty cells within the range all!B2:B5514?

--
Biff
Microsoft Excel MVP


"Paulo" wrote in message
...
This question was posted before but the solution proposed used different
functions. I would like to insist. Is there a modification to the formula
below, using SUMPRODUCT, that allows to retrieve the number of unique
itens
that meet a 2nd criteria?

Original formula:
=SUMPRODUCT((A1:A20<"")/COUNTIF(A1:A20,A1:A20&""))

In my case, I need the numbers of unique branches (column B) that meet a
specifc criteria in another column (M). So, I have the formula:

=SUMPRODUCT((all!$B$2:$B$5514<"")/COUNTIF(all!$B$2:$B$5514,all!$B$2:$B$5514&""))

but I need to include the 2nd criteria:

(all!$M$2:$M$5514=$A22)


Thanks in advance,

Paulo

Retrieve unique items with 2 criteria (USING SUMPRODUCT)
 

No, but then the first part of the function (all!$B$2:$B$5514<"") would have
taken care of that.

Let's assume there are no empty cells, if this helps.

Thanks,

Paulo


"T. Valko" wrote:

Are there any empty cells within the range all!B2:B5514?

--
Biff
Microsoft Excel MVP


"Paulo" wrote in message
...
This question was posted before but the solution proposed used different
functions. I would like to insist. Is there a modification to the formula
below, using SUMPRODUCT, that allows to retrieve the number of unique
itens
that meet a 2nd criteria?

Original formula:
=SUMPRODUCT((A1:A20<"")/COUNTIF(A1:A20,A1:A20&""))

In my case, I need the numbers of unique branches (column B) that meet a
specifc criteria in another column (M). So, I have the formula:

=SUMPRODUCT((all!$B$2:$B$5514<"")/COUNTIF(all!$B$2:$B$5514,all!$B$2:$B$5514&""))

but I need to include the 2nd criteria:

(all!$M$2:$M$5514=$A22)


Thanks in advance,

Paulo




.

Retrieve unique items with 2 criteria (USING SUMPRODUCT)
 

Try this array formula** :

=SUM(IF(FREQUENCY(IF(All!M2:M5514=A22,MATCH(All!B2 :B5514,All!B2:B5514,0)),ROW(All!B2:B5514)-ROW(All!B2)+1),1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Paulo" wrote in message
...
No, but then the first part of the function (all!$B$2:$B$5514<"") would
have
taken care of that.

Let's assume there are no empty cells, if this helps.

Thanks,

Paulo


"T. Valko" wrote:

Are there any empty cells within the range all!B2:B5514?

--
Biff
Microsoft Excel MVP


"Paulo" wrote in message
...
This question was posted before but the solution proposed used
different
functions. I would like to insist. Is there a modification to the
formula
below, using SUMPRODUCT, that allows to retrieve the number of unique
itens
that meet a 2nd criteria?

Original formula:
=SUMPRODUCT((A1:A20<"")/COUNTIF(A1:A20,A1:A20&""))

In my case, I need the numbers of unique branches (column B) that meet
a
specifc criteria in another column (M). So, I have the formula:

=SUMPRODUCT((all!$B$2:$B$5514<"")/COUNTIF(all!$B$2:$B$5514,all!$B$2:$B$5514&""))

but I need to include the 2nd criteria:

(all!$M$2:$M$5514=$A22)


Thanks in advance,

Paulo




.

Retrieve unique items with 2 criteria (USING SUMPRODUCT)
 

Thanks, but I am already aware of this formula. There was a previous post
where this solution was proposed. What I really wanted was a way of getting
the same result using SUMPRODUCT, but I am afraid it might not be possible.

Regards,

Paulo

"T. Valko" wrote:

Try this array formula** :

=SUM(IF(FREQUENCY(IF(All!M2:M5514=A22,MATCH(All!B2 :B5514,All!B2:B5514,0)),ROW(All!B2:B5514)-ROW(All!B2)+1),1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Paulo" wrote in message
...
No, but then the first part of the function (all!$B$2:$B$5514<"") would
have
taken care of that.

Let's assume there are no empty cells, if this helps.

Thanks,

Paulo


"T. Valko" wrote:

Are there any empty cells within the range all!B2:B5514?

--
Biff
Microsoft Excel MVP


"Paulo" wrote in message
...
This question was posted before but the solution proposed used
different
functions. I would like to insist. Is there a modification to the
formula
below, using SUMPRODUCT, that allows to retrieve the number of unique
itens
that meet a 2nd criteria?

Original formula:
=SUMPRODUCT((A1:A20<"")/COUNTIF(A1:A20,A1:A20&""))

In my case, I need the numbers of unique branches (column B) that meet
a
specifc criteria in another column (M). So, I have the formula:

=SUMPRODUCT((all!$B$2:$B$5514<"")/COUNTIF(all!$B$2:$B$5514,all!$B$2:$B$5514&""))

but I need to include the 2nd criteria:

(all!$M$2:$M$5514=$A22)


Thanks in advance,

Paulo




.



.

Retrieve unique items with 2 criteria (USING SUMPRODUCT)
 

the same result using SUMPRODUCT,
but I am afraid it might not be possible.

I'm sure it's *possible* but the formula I suggested is the *most efficient*
at counting uniques if using only the built-in functions. A SUMPRODUCT
version would not be very efficient on 5000+ rows of data.

If you want the most efficient *method* possible then you'd need to go with
a VBA UDF (user defined function). The most efficient UDF that I know of is
included in a free add-in called Morefunc.xll.

--
Biff
Microsoft Excel MVP


"Paulo" wrote in message
...
Thanks, but I am already aware of this formula. There was a previous post
where this solution was proposed. What I really wanted was a way of
getting
the same result using SUMPRODUCT, but I am afraid it might not be
possible.

Regards,

Paulo

"T. Valko" wrote:

Try this array formula** :

=SUM(IF(FREQUENCY(IF(All!M2:M5514=A22,MATCH(All!B2 :B5514,All!B2:B5514,0)),ROW(All!B2:B5514)-ROW(All!B2)+1),1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Paulo" wrote in message
...
No, but then the first part of the function (all!$B$2:$B$5514<"")
would
have
taken care of that.

Let's assume there are no empty cells, if this helps.

Thanks,

Paulo


"T. Valko" wrote:

Are there any empty cells within the range all!B2:B5514?

--
Biff
Microsoft Excel MVP


"Paulo" wrote in message
...
This question was posted before but the solution proposed used
different
functions. I would like to insist. Is there a modification to the
formula
below, using SUMPRODUCT, that allows to retrieve the number of
unique
itens
that meet a 2nd criteria?

Original formula:
=SUMPRODUCT((A1:A20<"")/COUNTIF(A1:A20,A1:A20&""))

In my case, I need the numbers of unique branches (column B) that
meet
a
specifc criteria in another column (M). So, I have the formula:

=SUMPRODUCT((all!$B$2:$B$5514<"")/COUNTIF(all!$B$2:$B$5514,all!$B$2:$B$5514&""))

but I need to include the 2nd criteria:

(all!$M$2:$M$5514=$A22)


Thanks in advance,

Paulo




.



.