HOW TO TAKE SUM OF TEXT VALUES

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

adeel - 16 May 2007 16:40 GMT

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)

Reply to this Message

Peo Sjoblom - 16 May 2007 16:52 GMT

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

Signature

Regards,

Peo Sjoblom

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

Reply to this Message

adeel - 18 May 2007 16:20 GMT

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))
>
[quoted text clipped - 9 lines]
>> Pencil No
>> (take SUM of Pencils with Yes in B1)

Reply to this Message

Dave Peterson - 18 May 2007 16:24 GMT

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

Signature

Dave Peterson

Reply to this Message

adeel - 18 May 2007 18:11 GMT

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)
[quoted text clipped - 7 lines]
>> Message posted via OfficeKB.com
>> http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200705/1

Reply to this Message

roadkill - 16 May 2007 16:58 GMT

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

> 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
[quoted text clipped - 8 lines]
> Pencil No
> (take SUM of Pencils with Yes in B1)

Reply to this Message