software

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=quot;Hertvikquot;)*(J4:J200=quot;PL*quot;)*(E 4:E200))

Column B contains our different agencies. In this instance I want all
policies with the quot;Hertvikquot; 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 quot;PLquot;. However, the * wildcard is not working.
If I type in quot;PL Autoquot; instead of quot;PL*quot; it works fine. Is there a
reason that the wildcard is not working?

Thanks! --
JackH1976
------------------------------------------------------------------------
JackH1976's Profile: www.excelforum.com/member.php...oamp;userid=29922
View this thread: www.excelforum.com/showthread...hreadid=496164=SUMPRODUCT(--(B4:B200=quot;Hertvikquot;),--(left(J4:J200,2)=quot;PL*quot;),(E4:E200))

=sumproduct() likes to work with numbers.

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

JackH1976 wrote:
gt;
gt; I work at an Insurance Agency and am having a problem with an Excel
gt; formula for our application log. Here is the formula that is returning
gt; a zero value to me:
gt;
gt; =SUMPRODUCT((B4:B200=quot;Hertvikquot;)*(J4:J200=quot;PL*quot;)*(E 4:E200))
gt;
gt; Column B contains our different agencies. In this instance I want all
gt; policies with the quot;Hertvikquot; agency. Column J contains policy types
gt; (i.e. PL Auto, PL Home, CL BOP, etc.). I want to sum the numbers in
gt; column E for all of the policies with the Hertvik agency and start
gt; withe the policy type quot;PLquot;. However, the * wildcard is not working.
gt; If I type in quot;PL Autoquot; instead of quot;PL*quot; it works fine. Is there a
gt; reason that the wildcard is not working?
gt;
gt; Thanks!
gt;
gt; --
gt; JackH1976
gt; ------------------------------------------------------------------------
gt; JackH1976's Profile: www.excelforum.com/member.php...oamp;userid=29922
gt; View this thread: www.excelforum.com/showthread...hreadid=496164

--

Dave Peterson

Try...

=SUMPRODUCT((B4:B200=quot;Hertvikquot;)*(LEFT(J4:J200,2)=quot; PLquot;)*(E4:E200))

or

=SUMPRODUCT(--(B4:B200=quot;Hertvikquot;),--(LEFT(J4:J200,2)=quot;PLquot;),E4:E200)

The latter is somewhat more efficient.

Hope this helps!

In article gt;,
JackH1976 gt;
wrote:

gt; I work at an Insurance Agency and am having a problem with an Excel
gt; formula for our application log. Here is the formula that is returning
gt; a zero value to me:
gt;
gt; =SUMPRODUCT((B4:B200=quot;Hertvikquot;)*(J4:J200=quot;PL*quot;)*(E 4:E200))
gt;
gt; Column B contains our different agencies. In this instance I want all
gt; policies with the quot;Hertvikquot; agency. Column J contains policy types
gt; (i.e. PL Auto, PL Home, CL BOP, etc.). I want to sum the numbers in
gt; column E for all of the policies with the Hertvik agency and start
gt; withe the policy type quot;PLquot;. However, the * wildcard is not working.
gt; If I type in quot;PL Autoquot; instead of quot;PL*quot; it works fine. Is there a
gt; reason that the wildcard is not working?
gt;
gt; Thanks!

Dave had a small typo.

He really meant:

=SUMPRODUCT(--(B4:B200=quot;Hertvikquot;),--(LEFT(J4:J200,2)=quot;PLquot;),(E4:E200))

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
quot;Dave Petersonquot; gt; wrote in message
...
gt; =SUMPRODUCT(--(B4:B200=quot;Hertvikquot;),--(left(J4:J200,2)=quot;PL*quot;),(E4:E200))
gt;
gt; =sumproduct() likes to work with numbers.
gt;
gt; The -- converts True's and False's to 1's and 0's.
gt;
gt; JackH1976 wrote:
gt; gt;
gt; gt; I work at an Insurance Agency and am having a problem with an Excel
gt; gt; formula for our application log. Here is the formula that is returning
gt; gt; a zero value to me:
gt; gt;
gt; gt; =SUMPRODUCT((B4:B200=quot;Hertvikquot;)*(J4:J200=quot;PL*quot;)*(E 4:E200))
gt; gt;
gt; gt; Column B contains our different agencies. In this instance I want all
gt; gt; policies with the quot;Hertvikquot; agency. Column J contains policy types
gt; gt; (i.e. PL Auto, PL Home, CL BOP, etc.). I want to sum the numbers in
gt; gt; column E for all of the policies with the Hertvik agency and start
gt; gt; withe the policy type quot;PLquot;. However, the * wildcard is not working.
gt; gt; If I type in quot;PL Autoquot; instead of quot;PL*quot; it works fine. Is there a
gt; gt; reason that the wildcard is not working?
gt; gt;
gt; gt; Thanks!
gt; gt;
gt; gt; --
gt; gt; JackH1976
gt; gt; ------------------------------------------------------------------------
gt; gt; JackH1976's Profile:
www.excelforum.com/member.php...oamp;userid=29922
gt; gt; View this thread:
www.excelforum.com/showthread...hreadid=496164
gt;
gt; --
gt;
gt; Dave Peterson
Thanks a lot! That works!--
JackH1976
------------------------------------------------------------------------
JackH1976's Profile: www.excelforum.com/member.php...oamp;userid=29922
View this thread: www.excelforum.com/showthread...hreadid=496164Thanks for the correction, RD.

Ragdyer wrote:
gt;
gt; Dave had a small typo.
gt;
gt; He really meant:
gt;
gt; =SUMPRODUCT(--(B4:B200=quot;Hertvikquot;),--(LEFT(J4:J200,2)=quot;PLquot;),(E4:E200))
gt;
gt; --
gt; Regards,
gt;
gt; RD
gt;
gt; ---------------------------------------------------------------------------
gt; Please keep all correspondence within the NewsGroup, so all may benefit !
gt; ---------------------------------------------------------------------------
gt; quot;Dave Petersonquot; gt; wrote in message
gt; ...
gt; gt; =SUMPRODUCT(--(B4:B200=quot;Hertvikquot;),--(left(J4:J200,2)=quot;PL*quot;),(E4:E200))
gt; gt;
gt; gt; =sumproduct() likes to work with numbers.
gt; gt;
gt; gt; The -- converts True's and False's to 1's and 0's.
gt; gt;
gt; gt; JackH1976 wrote:
gt; gt; gt;
gt; gt; gt; I work at an Insurance Agency and am having a problem with an Excel
gt; gt; gt; formula for our application log. Here is the formula that is returning
gt; gt; gt; a zero value to me:
gt; gt; gt;
gt; gt; gt; =SUMPRODUCT((B4:B200=quot;Hertvikquot;)*(J4:J200=quot;PL*quot;)*(E 4:E200))
gt; gt; gt;
gt; gt; gt; Column B contains our different agencies. In this instance I want all
gt; gt; gt; policies with the quot;Hertvikquot; agency. Column J contains policy types
gt; gt; gt; (i.e. PL Auto, PL Home, CL BOP, etc.). I want to sum the numbers in
gt; gt; gt; column E for all of the policies with the Hertvik agency and start
gt; gt; gt; withe the policy type quot;PLquot;. However, the * wildcard is not working.
gt; gt; gt; If I type in quot;PL Autoquot; instead of quot;PL*quot; it works fine. Is there a
gt; gt; gt; reason that the wildcard is not working?
gt; gt; gt;
gt; gt; gt; Thanks!
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; JackH1976
gt; gt; gt; ------------------------------------------------------------------------
gt; gt; gt; JackH1976's Profile:
gt; www.excelforum.com/member.php...oamp;userid=29922
gt; gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=496164
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson

--

Dave Peterson