Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO TAKE SUM OF TEXT VALUES
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) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200705/1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO TAKE SUM OF TEXT VALUES
=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")) -- Regards, Peo Sjoblom "adeel via OfficeKB.com" <u32736@uwe wrote in message news:7244ab3129bc1@uwe... 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) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200705/1 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO TAKE SUM OF TEXT VALUES
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" wrote: 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) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200705/1 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO TAKE SUM OF TEXT VALUES
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO TAKE SUM OF TEXT VALUES
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO TAKE SUM OF TEXT VALUES
Thanksssssssssss aloooooooooooooot.......................great my problem is
solved I need this formula =SUMPRODUCT(--(A1:A10="Pen"),--(B1:B10="Yes"),--(c1: c20=20)) thanks......:) Dave Peterson wrote: 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.a...excel/200705/1 -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sum values in a row if the value in a row above = text | Excel Worksheet Functions | |||
TEXT VALUES !!! | Excel Discussion (Misc queries) | |||
Top 10 Text Values | Excel Worksheet Functions | |||
Text values to numeric values | Excel Discussion (Misc queries) | |||
Text Values | Excel Discussion (Misc queries) |