Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Num of unique items in col? by 3 criteria | Excel Worksheet Functions | |||
SUMPRODUCT unique items | Excel Discussion (Misc queries) | |||
Counting Unique Items with Multiple Criteria | Excel Worksheet Functions | |||
Counting Unique Items with Multiple Criteria | Excel Worksheet Functions | |||
retrieve unique items with 2 criteria | Excel Worksheet Functions |