HOW TO TAKE SUM OF TEXT VALUES

[adeel via OfficeKB.com]

Hi. I have set of Text Values in 2 Columns (eg A1 & B1), in A1 the value are
like (eg Pen, Pencil) & in B1 values are 'Yes' (in some cell) 'No' (in some
cell). I want to take Sum of 'Pen' which have 'Yes' in Coloumn B1, & Sum of
'Pencil' which have 'Yes' in Column B1, and same with 'No'...hope some one
understade this...and help me....thanks...
A1 B1

Pen Yes
Pencil Yes
Pen No
Pencil No
(take SUM of Pencils with Yes in B1)

 

[Peo Sjoblom]

=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"))

 

[Guest]

This looks like a job for an array function. Type the expression below in
the cell where you want the total number of rows with "Pen" and "Yes" and
then hit CTL-SHIFT-ENTER (rather than just ENTER). Squiggly brackets ("{}")
should appear around the expression in the edit window.

=SUM((A1:A4="Pen")*(B1:B4="Yes")*1)

Adjust the range in this expression as needed. Create similar expressions
for the other combinations by substituting for "Pen" and/or "Yes".

Will

 

[adeel via OfficeKB.com]

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

=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)

 

[Dave Peterson]

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))

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

=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)

 

[adeel via OfficeKB.com]

Thanksssssssssss aloooooooooooooot.......................great my problem is
solved

I need this formula =SUMPRODUCT(--(A1:A10="Pen"),--(B1:B10="Yes"),--(c1:
c20=20))

thanks......

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))

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

[quoted text clipped - 29 lines]

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