| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM uniques
Nice one Biff! Although I think the formula may need to be tweaked,
somewhat. For example, if we have the following data... COPE & DRAG ( #3 ) 0.31 COPE & DRAG ( #3 ) 0.06 SINTO (C&D #4) 0.15 SINTO (C&D #4) 0.29 SINTO (C&D #4) 0.29 SINTO (C&D #4) 0.01 COPE & DRAG ( #3 ) 0.31 COPE & DRAG ( #3 ) 0.06 SINTO (C&D #4) 0.15 SINTO (C&D #4) 0.29 SINTO (C&D #4) 0.29 SINTO (C&D #4) 0.01 COPE & DRAG ( #2 ) 0.39 COPE & DRAG ( #2 ) 0.79 COPE & DRAG ( #6 ) 0.44 COPE & DRAG ( #6 ) 0.06 COPE & DRAG ( #2 ) 0.39 COPE & DRAG ( #2 ) 0.79 ....the formula will return 1.99. I believe the correct result should be 2.05. Notice that 0.06 appears in both #3 and #6. Maybe... =SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(D1,A3:A31)),M ATCH(A3:A31&"@"&B3:B31, A3:A31&"@"&B3:B31,0)),ROW(B3:B31)-ROW(B3)+1)0,B3:B31)) ....confirmed with CONTROL+SHIFT+ENTER. In article , "Biff" wrote: OK, I think I have it. One thing, though. You seem to be "determined" that you NEED to use wildcards. Not true, and, you have more flexibility when using a cell to hold your criteria. Based on the last sample data you posted. D1 = cope & drag Array entered =SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(D1,A3:A39)),M ATCH(B3:B39&"",B3:B39&"",0)) ,MATCH(B3:B39&"",B3:B39&"",0))0,B3:B40)) You'll notice the last range reference is one row longer than all the other range references. This is intentional and it doesn't matter if there is an entry in that cell or not. This is needed as a "bin dump" for the Frequency function. Biff |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM uniques
Hi Domenic thank you for your reply. Biff and Domenic Thank you for the formulas. But There is a problem. what If I want to total "SINTO" both of the formulas wont work. COPE & DRAG ( #3 ) 0.31 COPE & DRAG ( #3 ) 0.06 SINTO (C&D #4) 0.15 SINTO (C&D #4) 0.29 SINTO (C&D #4) 0.29 SINTO (C&D #4) 0.01 COPE & DRAG ( #3 ) 0.31 COPE & DRAG ( #3 ) 0.06 SINTO (C&D #4) 0.15 SINTO (C&D #4) 0.29 SINTO (C&D #4) 0.29 SINTO (C&D #4) 0.01 COPE & DRAG ( #2 ) 0.39 COPE & DRAG ( #2 ) 0.79 COPE & DRAG ( #6 ) 0.44 COPE & DRAG ( #6 ) 0.06 COPE & DRAG ( #2 ) 0.39 COPE & DRAG ( #2 ) 0.79 You and Biff formulas gives the wrong total. It gives me: 0.45 The correct total is 0.74 Any way we can fix this? -- Fin Fang Foom ------------------------------------------------------------------------ Fin Fang Foom's Profile: http://www.excelforum.com/member.php...o&userid=36516 View this thread: http://www.excelforum.com/showthread...hreadid=562724 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM uniques
Fin Fang Foom Wrote: Hi Domenic thank you for your reply. Biff and Domenic Thank you for the formulas. But There is a problem. what If I want to total "SINTO" both of the formulas wont work. COPE & DRAG ( #3 ) 0.31 COPE & DRAG ( #3 ) 0.06 SINTO (C&D #4) 0.15 SINTO (C&D #4) 0.29 SINTO (C&D #4) 0.29 SINTO (C&D #4) 0.01 COPE & DRAG ( #3 ) 0.31 COPE & DRAG ( #3 ) 0.06 SINTO (C&D #4) 0.15 SINTO (C&D #4) 0.29 SINTO (C&D #4) 0.29 SINTO (C&D #4) 0.01 COPE & DRAG ( #2 ) 0.39 COPE & DRAG ( #2 ) 0.79 COPE & DRAG ( #6 ) 0.44 COPE & DRAG ( #6 ) 0.06 COPE & DRAG ( #2 ) 0.39 COPE & DRAG ( #2 ) 0.79 You and Biff formulas gives the wrong total. It gives me: 0.45 The correct total is 0.74 Any way we can fix this? Any luck to get this formula to work? =SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("SINTO",A3:A3 1)),MATCH(A3:A31&"@"&B3:B31, A3:A31&"@"&B3:B31,0)),ROW(B3:B31)-ROW(B3)+1)0,B3:B31)) -- Fin Fang Foom ------------------------------------------------------------------------ Fin Fang Foom's Profile: http://www.excelforum.com/member.php...o&userid=36516 View this thread: http://www.excelforum.com/showthread...hreadid=562724 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM uniques
I think the formula may need to be tweaked, somewhat.
Yeah, I thought about that after I had posted. My formula is checking the unique number values and not the unique entries from column A. Biff .. "Domenic" wrote in message ... Nice one Biff! Although I think the formula may need to be tweaked, somewhat. For example, if we have the following data... COPE & DRAG ( #3 ) 0.31 COPE & DRAG ( #3 ) 0.06 SINTO (C&D #4) 0.15 SINTO (C&D #4) 0.29 SINTO (C&D #4) 0.29 SINTO (C&D #4) 0.01 COPE & DRAG ( #3 ) 0.31 COPE & DRAG ( #3 ) 0.06 SINTO (C&D #4) 0.15 SINTO (C&D #4) 0.29 SINTO (C&D #4) 0.29 SINTO (C&D #4) 0.01 COPE & DRAG ( #2 ) 0.39 COPE & DRAG ( #2 ) 0.79 COPE & DRAG ( #6 ) 0.44 COPE & DRAG ( #6 ) 0.06 COPE & DRAG ( #2 ) 0.39 COPE & DRAG ( #2 ) 0.79 ...the formula will return 1.99. I believe the correct result should be 2.05. Notice that 0.06 appears in both #3 and #6. Maybe... =SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(D1,A3:A31)),M ATCH(A3:A31&"@"&B3:B31, A3:A31&"@"&B3:B31,0)),ROW(B3:B31)-ROW(B3)+1)0,B3:B31)) ...confirmed with CONTROL+SHIFT+ENTER. In article , "Biff" wrote: OK, I think I have it. One thing, though. You seem to be "determined" that you NEED to use wildcards. Not true, and, you have more flexibility when using a cell to hold your criteria. Based on the last sample data you posted. D1 = cope & drag Array entered =SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(D1,A3:A39)),M ATCH(B3:B39&"",B3:B39&"",0)) ,MATCH(B3:B39&"",B3:B39&"",0))0,B3:B40)) You'll notice the last range reference is one row longer than all the other range references. This is intentional and it doesn't matter if there is an entry in that cell or not. This is needed as a "bin dump" for the Frequency function. Biff |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count number of uniques starting with a given letter? | Excel Discussion (Misc queries) | |||
| Count Uniques within a list based on value of cell... | Excel Discussion (Misc queries) | |||
| How to count uniques of a SUMPRODUCT subset? | Excel Worksheet Functions | |||
Hybrid Mode
