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

E

enna49

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
 
D

Dave Peterson

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

Peo Sjoblom

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
 
Top