Adding different text values
Hi:
I need to add all business lines - ie. A, B, C, D that are in a column. A, B, C need to be added together D needs to be added on it's own. This is what I ultimately need to know: IF my lines of business are as follows- a a b c c d d c a Then my first total would be 7 for the a b c's Then my second total would be 2 for the d's I am using 2003 version. Thank you in advance for your help. Cindy |
Adding different text values
Do you mean
=SUMPRODUCT(--(A1:A100={"a","b","c"})) =COUNTIF(A:A,"d") If this post helps click Yes --------------- Jacob Skaria "Cindyt" wrote: Hi: I need to add all business lines - ie. A, B, C, D that are in a column. A, B, C need to be added together D needs to be added on it's own. This is what I ultimately need to know: IF my lines of business are as follows- a a b c c d d c a Then my first total would be 7 for the a b c's Then my second total would be 2 for the d's I am using 2003 version. Thank you in advance for your help. Cindy |
Adding different text values
hi,
let's say your letters are in column A, and you want the result of the ABC in cell B2 and the result for the D in cell B3 in cell B2 enter =sumproduct(($A$1:$A$1000="A")+($A$1:$A$1000="B")+ ($A$1:$A$1000="C") in cell B3 enter =sumproduct(($A$1:$A$1000="D")) "Cindyt" wrote: Hi: I need to add all business lines - ie. A, B, C, D that are in a column. A, B, C need to be added together D needs to be added on it's own. This is what I ultimately need to know: IF my lines of business are as follows- a a b c c d d c a Then my first total would be 7 for the a b c's Then my second total would be 2 for the d's I am using 2003 version. Thank you in advance for your help. Cindy |
Adding different text values
Thank you so much!!!! Works like a charm!!
cindy "Jacob Skaria" wrote: Do you mean =SUMPRODUCT(--(A1:A100={"a","b","c"})) =COUNTIF(A:A,"d") If this post helps click Yes --------------- Jacob Skaria "Cindyt" wrote: Hi: I need to add all business lines - ie. A, B, C, D that are in a column. A, B, C need to be added together D needs to be added on it's own. This is what I ultimately need to know: IF my lines of business are as follows- a a b c c d d c a Then my first total would be 7 for the a b c's Then my second total would be 2 for the d's I am using 2003 version. Thank you in advance for your help. Cindy |
Adding different text values
Jacob - one other question - I need to differentiate an employee ID along
with the other things contained below. In other words, I need to see all the a, b, c, d's for employee id cj3 a cj3 a cj3 b kw2 c cj3 c kw2 d kw2 d cj3 so, cj3 would have 3 of the a,b,c's and 1 d Thank you so much again! "Jacob Skaria" wrote: Do you mean =SUMPRODUCT(--(A1:A100={"a","b","c"})) =COUNTIF(A:A,"d") If this post helps click Yes --------------- Jacob Skaria "Cindyt" wrote: Hi: I need to add all business lines - ie. A, B, C, D that are in a column. A, B, C need to be added together D needs to be added on it's own. This is what I ultimately need to know: IF my lines of business are as follows- a a b c c d d c a Then my first total would be 7 for the a b c's Then my second total would be 2 for the d's I am using 2003 version. Thank you in advance for your help. Cindy |
Adding different text values
Another way ... just keeping the functions consistent:
=Sum(Countif(A1:A100,{"A","B","C"})) =Countif(A1:A100,"D") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Cindyt" wrote in message ... Hi: I need to add all business lines - ie. A, B, C, D that are in a column. A, B, C need to be added together D needs to be added on it's own. This is what I ultimately need to know: IF my lines of business are as follows- a a b c c d d c a Then my first total would be 7 for the a b c's Then my second total would be 2 for the d's I am using 2003 version. Thank you in advance for your help. Cindy |
Adding different text values
Use a cell to hold the employee ID, say C1, then try these:
=SUMPRODUCT((A1:A100={"A","B","C"})*(B1:B100=C1)) =SUMPRODUCT((A1:A100="D")*(B1:B100=C1)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Cindyt" wrote in message ... Jacob - one other question - I need to differentiate an employee ID along with the other things contained below. In other words, I need to see all the a, b, c, d's for employee id cj3 a cj3 a cj3 b kw2 c cj3 c kw2 d kw2 d cj3 so, cj3 would have 3 of the a,b,c's and 1 d Thank you so much again! "Jacob Skaria" wrote: Do you mean =SUMPRODUCT(--(A1:A100={"a","b","c"})) =COUNTIF(A:A,"d") If this post helps click Yes --------------- Jacob Skaria "Cindyt" wrote: Hi: I need to add all business lines - ie. A, B, C, D that are in a column. A, B, C need to be added together D needs to be added on it's own. This is what I ultimately need to know: IF my lines of business are as follows- a a b c c d d c a Then my first total would be 7 for the a b c's Then my second total would be 2 for the d's I am using 2003 version. Thank you in advance for your help. Cindy |
Adding different text values
One more..............if all you have is a, b, c, d
=COUNTA(A1:A10)-COUNTIF(A1:A10,"d") =COUNTIF(A1:A10,"d") Gord Dibben MS Excel MVP On Tue, 4 Aug 2009 11:27:02 -0700, Cindyt wrote: Thank you so much!!!! Works like a charm!! cindy "Jacob Skaria" wrote: Do you mean =SUMPRODUCT(--(A1:A100={"a","b","c"})) =COUNTIF(A:A,"d") If this post helps click Yes --------------- Jacob Skaria "Cindyt" wrote: Hi: I need to add all business lines - ie. A, B, C, D that are in a column. A, B, C need to be added together D needs to be added on it's own. This is what I ultimately need to know: IF my lines of business are as follows- a a b c c d d c a Then my first total would be 7 for the a b c's Then my second total would be 2 for the d's I am using 2003 version. Thank you in advance for your help. Cindy |
All times are GMT +1. The time now is 08:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com