Wildcard Problem....

[JackH1976]

I work at an Insurance Agency and am having a problem with an Exce
formula for our application log. Here is the formula that is returnin
a zero value to me:

=SUMPRODUCT((B4:B200="Hertvik")*(J4:J200="PL*")*(E4:E200))

Column B contains our different agencies. In this instance I want al
policies with the "Hertvik" agency. Column J contains policy type
(i.e. PL Auto, PL Home, CL BOP, etc.). I want to sum the numbers i
column E for all of the policies with the Hertvik agency and star
withe the policy type "PL". However, the * wildcard is not working.
If I type in "PL Auto" instead of "PL*" it works fine. Is there
reason that the wildcard is not working?

Thanks! :confused

 

[Dave Peterson]

=SUMPRODUCT(--(B4:B200="Hertvik"),--(left(J4:J200,2)="PL*"),(E4:E200))

=sumproduct() likes to work with numbers.

The -- converts True's and False's to 1's and 0's.

 

[Domenic]

Try...

=SUMPRODUCT((B4:B200="Hertvik")*(LEFT(J4:J200,2)="PL")*(E4:E200))

or

=SUMPRODUCT(--(B4:B200="Hertvik"),--(LEFT(J4:J200,2)="PL"),E4:E200)

The latter is somewhat more efficient.

Hope this helps!

 

[Ragdyer]

Dave had a small typo.

He really meant:

=SUMPRODUCT(--(B4:B200="Hertvik"),--(LEFT(J4:J200,2)="PL"),(E4:E200))

 

[JackH1976]

Thanks a lot! That works

 

[Dave Peterson]

Thanks for the correction, RD.

Dave had a small typo.

He really meant:

=SUMPRODUCT(--(B4:B200="Hertvik"),--(LEFT(J4:J200,2)="PL"),(E4:E200))