SUM uniques
 

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

SUM uniques
 

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

SUM uniques
 

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

SUM uniques
 

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

SUM uniques
 

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

SUM uniques
 

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

SUM uniques
 

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

SUM uniques
 

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

SUM uniques
 

Anyone else want to get this formula to work as a wildcard?


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


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

SUM uniques
 

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:B3 9&"",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

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

Anyone else want to get this formula to work as a wildcard?


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


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

SUM uniques
 

Thank you so much Biff it works perfect. I want to thank you even more
for taking your time to get that formula to work.

Once again thank you!


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

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

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

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

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