Logical And'ing over a range

[ExcellantA]

I'm being fustrated by something that should be fair easy :/

In a spread sheet i've 3 columns 2 of text 1 of numbers. eg;

A B C
m op 10
t op 12
m op 22
m mac 33

what i'm after doing is adding up the qty in C when theres both m and op in a row.

If it was just 1 column it'd be a simple =sumif(a:a,"m",c:c), with using 'AND' i can compare idividual rows, but i can't seem to get it to add up over the entire range.

[EdMac]

Hi,

You need to use sumproduct

=SUMPRODUCT(--(A1:A10="m")*(b1:b10="op"),(c1:c10)

Note, adjust your ranges to suit - all ranges must be the same length

ED

[ExcellantA]

Thanks that works great. Although what's the significance of the --?

i tested removing it from my forumula and it seemed to have no affect.

[EdMac]

Glad to hear it works

-- is what is called a double unary operator - you need it if you are looking at text value.

SUMPRODUCT is described in some useful detail at

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Its worth a read.

Ed