software

I am trying to SUM col3 if 2 cols meet a criteria eg If COL 1 = 2222 amp; col 2
= ABCDE then sum col3. Is this possible

Thanks

=sumproduct(--(a1:a10=2222),--(b1:b10=quot;abcdequot;),(c1:c10))

Adjust the range, but don't use the whole column.

=sumproduct() likes to work with numbers. The -- stuff changes true and false
to 1 and 0.

enna49 wrote:
gt;
gt; I am trying to SUM col3 if 2 cols meet a criteria eg If COL 1 = 2222 amp; col 2
gt; = ABCDE then sum col3. Is this possible
gt;
gt; Thanks

--

Dave Peterson

Hi Dave
I gather SUMPRODUCT does not like * (WILD) either as SUMIF will accept

quot;Dave Petersonquot; wrote:

gt; =sumproduct(--(a1:a10=2222),--(b1:b10=quot;abcdequot;),(c1:c10))
gt;
gt; Adjust the range, but don't use the whole column.
gt;
gt; =sumproduct() likes to work with numbers. The -- stuff changes true and false
gt; to 1 and 0.
gt;
gt; enna49 wrote:
gt; gt;
gt; gt; I am trying to SUM col3 if 2 cols meet a criteria eg If COL 1 = 2222 amp; col 2
gt; gt; = ABCDE then sum col3. Is this possible
gt; gt;
gt; gt; Thanks
gt;
gt; --
gt;
gt; Dave Peterson
gt;

You can use other means, for instance if you want to get something that
starts with abc you that would be quot;abc*quot; in SUMIF

=SUMPRODUCT(--(LEFT(A2:A20,3)=quot;abcquot;),B2:B20)

using right would be equivalent of quot;*abcquot;

another way equivalent of quot;*abc*quot; in sumif finding abc anywhere

=SUMPRODUCT(--(ISNUMBER(SEARCH(quot;abcquot;,A2:A20))),B2:B20)

change search to find and you'll get a case sensitive criteria--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

quot;enna49quot; gt; wrote in message
...
gt; Hi Dave
gt; I gather SUMPRODUCT does not like * (WILD) either as SUMIF will accept
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt;gt; =sumproduct(--(a1:a10=2222),--(b1:b10=quot;abcdequot;),(c1:c10))
gt;gt;
gt;gt; Adjust the range, but don't use the whole column.
gt;gt;
gt;gt; =sumproduct() likes to work with numbers. The -- stuff changes true and
gt;gt; false
gt;gt; to 1 and 0.
gt;gt;
gt;gt; enna49 wrote:
gt;gt; gt;
gt;gt; gt; I am trying to SUM col3 if 2 cols meet a criteria eg If COL 1 = 2222 amp;
gt;gt; gt; col 2
gt;gt; gt; = ABCDE then sum col3. Is this possible
gt;gt; gt;
gt;gt; gt; Thanks
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Dave Peterson
gt;gt;