Hi!
Try this:
=SUMIF(A3:A39,"cope &
drag*",B3:B39)/SUM(--(FREQUENCY(IF(ISNUMBER(SEARCH("cope &
drag",A3:A39)),MATCH(A3:A39&"",A3:A39&"",0)),MATCH (A3:A39&"",A3:A39&"",0))0))
Or, using a cell to hold the criteria (E42 as used in your other formula):
E42 = cope & drag
=SUMIF(A3:A39,E42&"*",B3:B39)/SUM(--(FREQUENCY(IF(ISNUMBER(SEARCH(E42,A3:A39)),MATCH(A 3:A39&"",A3:A39&"",0)),MATCH(A3:A39&"",A3:A39&"",0 ))0))
Both are array formulas.
Biff
"Fin Fang Foom"
wrote in message
news:Fin.Fang.Foom.2b6c67_1153281610.3383@excelfor um-nospam.com...
Hi everyone,
Well I got this formula it was provided to me by Domenic and it works
great. But I would like to have a wildcard in place in the formula.
I also posted on this forum.
http://www.ozgrid.com/forum/showthre...t=53927&page=2
=SUMIF(A3:A39,E42,B3:B39)/COUNT(1/FREQUENCY(IF(A3:A39=E42,ROW(A3:A39)),IF(A3:A39<E4 2,ROW(A3:A39))))
I tried to modified to this but no luck.
=SUMIF(A3:A39,"COPE &
DRAG*",B3:B39)/COUNT(1/FREQUENCY(IF((LEFT(A3:A39,11)="COPE &
DRAG"),ROW(A3:A39)),IF((LEFT(A3:A39,11)="COPE & DRAG"),ROW(A3:A39))))
Example, My data is structure to this
A B
COPE & DRAG ( #3 ) 0.31
COPE & DRAG ( #3 ) 0.06
COPE & DRAG ( #2 ) 1
COPE & DRAG ( #2 ) 0
COPE & DRAG ( #3 ) 0.31
COPE & DRAG ( #3 ) 0.06
COPE & DRAG ( #2 ) 1
COPE & DRAG ( #2 ) 0
The Total shoud be 1.37
Any Ideas?
--
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