If 2 cols meet a criteria then sum the 3rd column
 

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

Thanks

If 2 cols meet a criteria then sum the 3rd column
 

=sumproduct(--(a1:a10=2222),--(b1:b10="abcde"),(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:

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

Thanks

--

Dave Peterson

If 2 cols meet a criteria then sum the 3rd column
 

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

"Dave Peterson" wrote:

=sumproduct(--(a1:a10=2222),--(b1:b10="abcde"),(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:

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

Thanks

--

Dave Peterson

If 2 cols meet a criteria then sum the 3rd column
 

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

=SUMPRODUCT(--(LEFT(A2:A20,3)="abc"),B2:B20)

using right would be equivalent of "*abc"

another way equivalent of "*abc*" in sumif finding abc anywhere

=SUMPRODUCT(--(ISNUMBER(SEARCH("abc",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




"enna49" wrote in message
...
Hi Dave
I gather SUMPRODUCT does not like * (WILD) either as SUMIF will accept

"Dave Peterson" wrote:

=sumproduct(--(a1:a10=2222),--(b1:b10="abcde"),(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:

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

Thanks

--

Dave Peterson