Do you mean sum column C for Pen and Yes?
=SUMPRODUCT(--(A1:A10="Pen"),--(B1:B10="Yes"),c1:c10)

If you really meant that you wanted to count Pen/Yes/20 (unlikely, huh?):
=SUMPRODUCT(--(A1:A10="Pen"),--(B1:B10="Yes"),--(c1:c20=20))


"adeel via OfficeKB.com" wrote:

Thanks alot this funtion works.... now i have a problem that i have more than
2 columns to compare & take SUM of values. for example

A B C

PEN YES 20
PENCIL YES 30
PEN YES 40
PEN YES 20
PEN NO 10
PENCIL NO 50

(I want to take the Total Nos. of PEN which are "YES" in Col 'B' & '20' in
Col. 'C')
Hope U understand.. Thanks...

Peo Sjoblom wrote:
=SUMPRODUCT(--(A1:A10=D2),--(B1:B10=E2))

where you would put Pen in D2 and Yes in E2
or you can hard code it but then you need to change the formula when you
change criteria

=SUMPRODUCT(--(A1:A10="Pen"),--(B1:B10="Yes"))

Hi. I have set of Text Values in 2 Columns (eg A1 & B1), in A1 the value
are
[quoted text clipped - 11 lines]
Pencil No
(take SUM of Pencils with Yes in B1)

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200705/1

--

Dave Peterson