Wildcard Problem....

I work at an Insurance Agency and am having a problem with an Excel formula for our application log. Here is the formula that is returning 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 all policies with the "Hertvik" agency. Column J contains policy types (i.e. PL Auto, PL Home, CL BOP, etc.). I want to sum the numbers in column E for all of the policies with the Hertvik agency and start 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 a reason that the wildcard is not working?

Thanks!

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

JackH1976 wrote:
>
> I work at an Insurance Agency and am having a problem with an Excel
> formula for our application log. Here is the formula that is returning
> 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 all
> policies with the "Hertvik" agency. Column J contains policy types
> (i.e. PL Auto, PL Home, CL BOP, etc.). I want to sum the numbers in
> column E for all of the policies with the Hertvik agency and start
> 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 a
> reason that the wildcard is not working?
>
> Thanks!
>
> --
> JackH1976
> ------------------------------------------------------------------------
> JackH1976's Profile: http://www.excelforum.com/member.php...o&userid=29922
> View this thread: http://www.excelforum.com/showthread...hreadid=496164

--

Dave Peterson

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!

In article <[email protected]>,
JackH1976 <[email protected]>
wrote:

> I work at an Insurance Agency and am having a problem with an Excel
> formula for our application log. Here is the formula that is returning
> 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 all
> policies with the "Hertvik" agency. Column J contains policy types
> (i.e. PL Auto, PL Home, CL BOP, etc.). I want to sum the numbers in
> column E for all of the policies with the Hertvik agency and start
> 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 a
> reason that the wildcard is not working?
>
> Thanks!

Dave had a small typo.

He really meant:

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

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> =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.
>
> JackH1976 wrote:
> >
> > I work at an Insurance Agency and am having a problem with an Excel
> > formula for our application log. Here is the formula that is returning
> > 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 all
> > policies with the "Hertvik" agency. Column J contains policy types
> > (i.e. PL Auto, PL Home, CL BOP, etc.). I want to sum the numbers in
> > column E for all of the policies with the Hertvik agency and start
> > 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 a
> > reason that the wildcard is not working?
> >
> > Thanks!
> >
> > --
> > JackH1976
> > ------------------------------------------------------------------------
> > JackH1976's Profile:
http://www.excelforum.com/member.php...o&userid=29922
> > View this thread:
http://www.excelforum.com/showthread...hreadid=496164
>
> --
>
> Dave Peterson

Thanks a lot! That works!

Thanks for the correction, RD.

Ragdyer wrote:
>
> Dave had a small typo.
>
> He really meant:
>
> =SUMPRODUCT(--(B4:B200="Hertvik"),--(LEFT(J4:J200,2)="PL"),(E4:E200))
>
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > =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.
> >
> > JackH1976 wrote:
> > >
> > > I work at an Insurance Agency and am having a problem with an Excel
> > > formula for our application log. Here is the formula that is returning
> > > 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 all
> > > policies with the "Hertvik" agency. Column J contains policy types
> > > (i.e. PL Auto, PL Home, CL BOP, etc.). I want to sum the numbers in
> > > column E for all of the policies with the Hertvik agency and start
> > > 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 a
> > > reason that the wildcard is not working?
> > >
> > > Thanks!
> > >
> > > --
> > > JackH1976
> > > ------------------------------------------------------------------------
> > > JackH1976's Profile:
> http://www.excelforum.com/member.php...o&userid=29922
> > > View this thread:
> http://www.excelforum.com/showthread...hreadid=496164
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson