sumproduct question 1

[Topcat04]

Hi all

Is there a way to do a sumproduct on word or number contained in cell

So instead of finding a cell that is equal using = and only has one word in the cell, is there a way to find all those that contain the word Licensed in the description? LIKE "Licensed"

To the same extent, we use codes that are sequential but the first 5 characters denote its group and I would like to use sumproduct with the group.
eg
N73451
N73452
N73453
N73454

I would like to sumproduct all that contains N7345

Hope I have explained myself?.

 

[xlhelp]

Yes. =SUMPRODUCT((LEFT(Range,5)="N7345")*........

Member- AAAA Association Against Acronym Abusers
Only here temporarily during job transition....again....but better this time.

 

[SkipVought]

Hi,

Use the LEFT function in conjunction with SUMPRODUCT...
[tt]
=SUMPRODUCT((LEFT(Key,5)=K2)*(Val))
[tt]


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Topcat04]

Thanks for the answers, got the Left function to work on the Code,

Still trying to work out how to sumproduct on a word in a description...if possible?

 

[AnotherHiggins]

=SumProduct( (Left($A$1:$A$10000, 5)="N7345") * IsNumber(Search("Licensed", $B$1:$B$10000)))

For more examples of using wildcards in various formulas, have a look at KenWright's post dated 14 Jul 06 2:36 in thread68-1253790.



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.