Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 47
Default 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


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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




  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 47
Default 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




.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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




.



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 47
Default 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




.



.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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




.



.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count Num of unique items in col? by 3 criteria CindyC Excel Worksheet Functions 5 April 25th 09 05:02 AM
SUMPRODUCT unique items Tufail Excel Discussion (Misc queries) 2 January 4th 09 08:15 PM
Counting Unique Items with Multiple Criteria Joe Gieder Excel Worksheet Functions 3 March 5th 07 11:08 PM
Counting Unique Items with Multiple Criteria Teethless mama Excel Worksheet Functions 0 March 3rd 07 12:12 AM
retrieve unique items with 2 criteria Dave Breitenbach Excel Worksheet Functions 10 December 1st 05 12:16 AM


All times are GMT +1. The time now is 06:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"