Retrieve unique items with 2 criteria (USING SUMPRODUCT)
in [Functions]
|
Prev: How do I get a sum of every seventh cell in a column?
Next: Formula results display only after pressing Enter key
From: Paulo on 25 Feb 2010 11:47 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
From: T. Valko on 25 Feb 2010 12:47 Are there any empty cells within the range all!B2:B5514? -- Biff Microsoft Excel MVP "Paulo" <Paulo(a)discussions.microsoft.com> wrote in message news:9B196D00-3457-4F53-BA95-B96F8E6B653C(a)microsoft.com... > 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 > >
From: Paulo on 26 Feb 2010 04:13 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" <Paulo(a)discussions.microsoft.com> wrote in message > news:9B196D00-3457-4F53-BA95-B96F8E6B653C(a)microsoft.com... > > 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 > > > > > > > . >
From: T. Valko on 26 Feb 2010 11:54 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" <Paulo(a)discussions.microsoft.com> wrote in message news:461059FE-C070-4E91-9F26-4D4ABF3935D9(a)microsoft.com... > 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" <Paulo(a)discussions.microsoft.com> wrote in message >> news:9B196D00-3457-4F53-BA95-B96F8E6B653C(a)microsoft.com... >> > 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 >> > >> > >> >> >> . >>
From: Paulo on 2 Mar 2010 09:01
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" <Paulo(a)discussions.microsoft.com> wrote in message > news:461059FE-C070-4E91-9F26-4D4ABF3935D9(a)microsoft.com... > > 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" <Paulo(a)discussions.microsoft.com> wrote in message > >> news:9B196D00-3457-4F53-BA95-B96F8E6B653C(a)microsoft.com... > >> > 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 > >> > > >> > > >> > >> > >> . > >> > > > . > |