Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve unique items with 2 criteria
I've been using the following formula from the xldynamic website detailing
sumproduct usage as a base for retrieving uniqe instances in a data series. I've been trying to modify the formula to allow me to utilize one additional criteria. Original formula: =SUMPRODUCT((A1:A20<"")/COUNTIF(A1:A20,A1:A20&"")) Here is my formula: =SUMPRODUCT((($O$7:$O$2710<"")*($M$7:$M$2710="198 6"))/COUNTIF($O$7:$O$2710,$O$7:$O$2710&"")) I'm trying to say how many unique instances are there for column O, where column m = "1986?" Not sure what I'm doing wrong but help would be appreciated. tia, Dave This seems to apply only the second criteria |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve unique items with 2 criteria
You don't need an argument for the "O array", try: =SUMPRODUCT((($O$7:$O$2710)*($M$7:$M$2710="1986"))/COUNTIF($O$7:$O$2710,$O$7:$O$2710&"")) -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=489194 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve unique items with 2 criteria
This gives me the #value error. Any other thoughts?
I thought the O qualifier was safeguarding against blank cells? "Vito" wrote: You don't need an argument for the "O array", try: =SUMPRODUCT((($O$7:$O$2710)*($M$7:$M$2710="1986"))/COUNTIF($O$7:$O$2710,$O$7:$O$2710&"")) -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=489194 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve unique items with 2 criteria
Dave,
You could use =SUM(--(FREQUENCY(IF(M7:M2710=1986,MATCH(O7:O2710,O7:O271 0,0)),ROW(INDIRECT( "1:"&ROWS(O7:O2710))))0)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "Dave Breitenbach" wrote in message ... I've been using the following formula from the xldynamic website detailing sumproduct usage as a base for retrieving uniqe instances in a data series. I've been trying to modify the formula to allow me to utilize one additional criteria. Original formula: =SUMPRODUCT((A1:A20<"")/COUNTIF(A1:A20,A1:A20&"")) Here is my formula: =SUMPRODUCT((($O$7:$O$2710<"")*($M$7:$M$2710="198 6"))/COUNTIF($O$7:$O$2710, $O$7:$O$2710&"")) I'm trying to say how many unique instances are there for column O, where column m = "1986?" Not sure what I'm doing wrong but help would be appreciated. tia, Dave This seems to apply only the second criteria |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve unique items with 2 criteria
Thanks Bob - this worked.
"Bob Phillips" wrote: Dave, You could use =SUM(--(FREQUENCY(IF(M7:M2710=1986,MATCH(O7:O2710,O7:O271 0,0)),ROW(INDIRECT( "1:"&ROWS(O7:O2710))))0)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "Dave Breitenbach" wrote in message ... I've been using the following formula from the xldynamic website detailing sumproduct usage as a base for retrieving uniqe instances in a data series. I've been trying to modify the formula to allow me to utilize one additional criteria. Original formula: =SUMPRODUCT((A1:A20<"")/COUNTIF(A1:A20,A1:A20&"")) Here is my formula: =SUMPRODUCT((($O$7:$O$2710<"")*($M$7:$M$2710="198 6"))/COUNTIF($O$7:$O$2710, $O$7:$O$2710&"")) I'm trying to say how many unique instances are there for column O, where column m = "1986?" Not sure what I'm doing wrong but help would be appreciated. tia, Dave This seems to apply only the second criteria |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve unique items with 2 criteria
Thanks Bob for helping out.:) -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=489194 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve unique items with 2 criteria
Alright. Question answered but I've got another one. Of the unique values
in column O, I've been trying to apply the small function to the formula below to give me the kth smallest value of the unique O column values. I've tried a few positions including the following but have not had any luck: =SMALL(--(FREQUENCY(IF(TRIM('prepay detail'!M7:M2710)=$B32,MATCH('prepay detail'!O7:O2710,'prepay detail'!O7:O2710,0)),ROW(INDIRECT( "1:"&ROWS('prepay detail'!O7:O2710))))0),1) any thoughts? tia, Dave "Bob Phillips" wrote: Dave, You could use =SUM(--(FREQUENCY(IF(M7:M2710=1986,MATCH(O7:O2710,O7:O271 0,0)),ROW(INDIRECT( "1:"&ROWS(O7:O2710))))0)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "Dave Breitenbach" wrote in message ... I've been using the following formula from the xldynamic website detailing sumproduct usage as a base for retrieving uniqe instances in a data series. I've been trying to modify the formula to allow me to utilize one additional criteria. Original formula: =SUMPRODUCT((A1:A20<"")/COUNTIF(A1:A20,A1:A20&"")) Here is my formula: =SUMPRODUCT((($O$7:$O$2710<"")*($M$7:$M$2710="198 6"))/COUNTIF($O$7:$O$2710, $O$7:$O$2710&"")) I'm trying to say how many unique instances are there for column O, where column m = "1986?" Not sure what I'm doing wrong but help would be appreciated. tia, Dave This seems to apply only the second criteria |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve unique items with 2 criteria
Try...
=SMALL(IF((M7:M100=1986)*(MATCH(M7:M100&"#"&O7:O10 0,M7:M100&"#"&O7:O100,0 )=ROW(O7:O100)-ROW(O7)+1),O7:O100),2) ....confirmed with CONTROL+SHIFT+ENTER. Adjust the range accordingly. Hope this helps! In article , "Dave Breitenbach" wrote: Alright. Question answered but I've got another one. Of the unique values in column O, I've been trying to apply the small function to the formula below to give me the kth smallest value of the unique O column values. I've tried a few positions including the following but have not had any luck: =SMALL(--(FREQUENCY(IF(TRIM('prepay detail'!M7:M2710)=$B32,MATCH('prepay detail'!O7:O2710,'prepay detail'!O7:O2710,0)),ROW(INDIRECT( "1:"&ROWS('prepay detail'!O7:O2710))))0),1) any thoughts? tia, Dave "Bob Phillips" wrote: Dave, You could use =SUM(--(FREQUENCY(IF(M7:M2710=1986,MATCH(O7:O2710,O7:O271 0,0)),ROW(INDIRECT( "1:"&ROWS(O7:O2710))))0)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "Dave Breitenbach" wrote in message ... I've been using the following formula from the xldynamic website detailing sumproduct usage as a base for retrieving uniqe instances in a data series. I've been trying to modify the formula to allow me to utilize one additional criteria. Original formula: =SUMPRODUCT((A1:A20<"")/COUNTIF(A1:A20,A1:A20&"")) Here is my formula: =SUMPRODUCT((($O$7:$O$2710<"")*($M$7:$M$2710="198 6"))/COUNTIF($O$7:$O$2710, $O$7:$O$2710&"")) I'm trying to say how many unique instances are there for column O, where column m = "1986?" Not sure what I'm doing wrong but help would be appreciated. tia, Dave This seems to apply only the second criteria |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve unique items with 2 criteria
You guys continue to impress. This is great.
I do have a couple of questions though. If I understand this correctly, the match formula can have a lookup value as an array instead of just a single value. If its lookup array and lookup value are the same, then it is simply giving an array of the position of each item in the total range used in the lookup value/lookup range. It appears you've concatenated the 2 criteria columns for the purpose of locating their commonalities in the array. This is intuitive looking back on it but clever without knowing it. Was the "#" an arbitrary choice for a connector? Why is it necessary to have any connector? thanks to both Domenic and Bob for this one! Dave "Domenic" wrote: Try... =SMALL(IF((M7:M100=1986)*(MATCH(M7:M100&"#"&O7:O10 0,M7:M100&"#"&O7:O100,0 )=ROW(O7:O100)-ROW(O7)+1),O7:O100),2) ....confirmed with CONTROL+SHIFT+ENTER. Adjust the range accordingly. Hope this helps! In article , "Dave Breitenbach" wrote: Alright. Question answered but I've got another one. Of the unique values in column O, I've been trying to apply the small function to the formula below to give me the kth smallest value of the unique O column values. I've tried a few positions including the following but have not had any luck: =SMALL(--(FREQUENCY(IF(TRIM('prepay detail'!M7:M2710)=$B32,MATCH('prepay detail'!O7:O2710,'prepay detail'!O7:O2710,0)),ROW(INDIRECT( "1:"&ROWS('prepay detail'!O7:O2710))))0),1) any thoughts? tia, Dave "Bob Phillips" wrote: Dave, You could use =SUM(--(FREQUENCY(IF(M7:M2710=1986,MATCH(O7:O2710,O7:O271 0,0)),ROW(INDIRECT( "1:"&ROWS(O7:O2710))))0)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "Dave Breitenbach" wrote in message ... I've been using the following formula from the xldynamic website detailing sumproduct usage as a base for retrieving uniqe instances in a data series. I've been trying to modify the formula to allow me to utilize one additional criteria. Original formula: =SUMPRODUCT((A1:A20<"")/COUNTIF(A1:A20,A1:A20&"")) Here is my formula: =SUMPRODUCT((($O$7:$O$2710<"")*($M$7:$M$2710="198 6"))/COUNTIF($O$7:$O$2710, $O$7:$O$2710&"")) I'm trying to say how many unique instances are there for column O, where column m = "1986?" Not sure what I'm doing wrong but help would be appreciated. tia, Dave This seems to apply only the second criteria |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve unique items with 2 criteria
The "#" is used to deal with situations where you have...
1,11 11,1 Without "#"... 111 111 With "#"... 1#11 11#1 Note that you can use other characters as well for this purpose. For example, you can also use "@". Hope this helps! In article , "Dave Breitenbach" wrote: You guys continue to impress. This is great. I do have a couple of questions though. If I understand this correctly, the match formula can have a lookup value as an array instead of just a single value. If its lookup array and lookup value are the same, then it is simply giving an array of the position of each item in the total range used in the lookup value/lookup range. It appears you've concatenated the 2 criteria columns for the purpose of locating their commonalities in the array. This is intuitive looking back on it but clever without knowing it. Was the "#" an arbitrary choice for a connector? Why is it necessary to have any connector? thanks to both Domenic and Bob for this one! Dave |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve unique items with 2 criteria
Great. Thanks again.
"Domenic" wrote: The "#" is used to deal with situations where you have... 1,11 11,1 Without "#"... 111 111 With "#"... 1#11 11#1 Note that you can use other characters as well for this purpose. For example, you can also use "@". Hope this helps! In article , "Dave Breitenbach" wrote: You guys continue to impress. This is great. I do have a couple of questions though. If I understand this correctly, the match formula can have a lookup value as an array instead of just a single value. If its lookup array and lookup value are the same, then it is simply giving an array of the position of each item in the total range used in the lookup value/lookup range. It appears you've concatenated the 2 criteria columns for the purpose of locating their commonalities in the array. This is intuitive looking back on it but clever without knowing it. Was the "#" an arbitrary choice for a connector? Why is it necessary to have any connector? thanks to both Domenic and Bob for this one! Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting unique records with additional criteria | Excel Worksheet Functions | |||
create an array with unique items IN MEMORY | Excel Worksheet Functions | |||
Count Unique Names in list w/ Additional Criteria? | Excel Worksheet Functions | |||
Unique values with criteria | Excel Worksheet Functions | |||
Count of unique items meeting condition | Excel Worksheet Functions |