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

Try This

=sumproduct((left(a3:a39,11)="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))))


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=562724

duane Wrote:
Try This

=sumproduct((left(a3:a39,11)="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))))



Hi Durane unfortunalty it did not work. I got 0.34.

It should 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

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

Hi Biff,


Thank you so much for replying. Your formula works but when I did some
scenarios I got a different value. Here is my data in a larger set.
This data fluctuates daily.


It totals up 1.56

A B
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


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



It should 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

Anyone could help ?


--
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

Make it easy on yourself! I HATE using helper cells myself but sometimes
that's what you HAVE to do.

Use a helper column:

Entered in C3:

=IF(A3="","",IF(ISNUMBER(SEARCH(E$42,A3)),IF(SUMPR ODUCT(--(A$3:A3&B$3:B3=A3&B3))1,"",B3),""))

Copy down as needed.

Then:

=SUM(C:C)

Returns 1.37

Biff

"Fin Fang Foom"
wrote in message
news:Fin.Fang.Foom.2b7hdh_1153335014.6875@excelfor um-nospam.com...

Anyone could help ?


--
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

Thank you Biff for taking your time on this. unfortunalty I hate helper
columns also but thats the only way.

Thats so werid this formula works:

=SUMIF(A3:A39,"COPE & DRAG ( #3
)",B3:B39)/COUNT(1/FREQUENCY(IF(A3:A39="COPE & DRAG ( #3
)",ROW(A3:A39)),IF(A3:A39<"COPE & DRAG ( #3 )" 2,ROW(A3:A39))))


But it can not be modified in a wild card formula.

That means there is not a formula out there that could do it..


--
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