Adding different text values

[Cindyt]

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

 

[Jacob Skaria]

Do you mean

=SUMPRODUCT(--(A1:A100={"a","b","c"}))
=COUNTIF(A:A,"d")

If this post helps click Yes

 

[Eduardo]

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]

Thank you so much!!!! Works like a charm!!
cindy

 

[Cindyt]

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!

 

[RagDyer]

Another way ... just keeping the functions consistent:

=Sum(Countif(A1:A100,{"A","B","C"}))

=Countif(A1:A100,"D")

 

[RagDyer]

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

 

[Gord Dibben]

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