Tally Text

[Sally]

I want to tally a column of text.
For example: If the word "Reject" appears 10 times in the
range A1:A10, I want it to return (10) Reject.
Is this possible?

Thank you

 

[Andy B]

Sally

Try using a pivot table (Data/Pivot Table). This is useful for summarising
information.

Andy

 

[Judy Freed]

If you just want a count of how many "Rejects" there are, you could use
COUNTIF

=COUNTIF(A1:A10,"Reject")


Judy Freed
Systems Development
UNC Charlotte

 

[Peo Sjoblom]

Use another cell where you type in the criteria (Reject)

="("&COUNTIF(A1:A10,B1)&") "&B1

or if you need to hard code

="("&COUNTIF(A1:A10,B1)&") Reject"

 

[Dave]

=+"("&COUNTIF(a1:a10,"*reject*")&") Reject"

Thank you for your help
I don't want to just count the number of rejects, I want
it to return "(10) Reject" exactly.
Is this possible?

 

[Dave]

We know that countif(range, "*reject*") counts the word "reject" or
"sdkfhreject", etc -- but I see that it doesn't count more than 1
occurence in any cell, e.g. countif *reject* on the rows below returns
3 instead of 4. Any way around this in formulas?

reject
rejectreject
reject

 

[Harlan Grove]

We know that countif(range, "*reject*") counts the word "reject" or
"sdkfhreject", etc -- but I see that it doesn't count more than 1
occurence in any cell, e.g. countif *reject* on the rows below returns
3 instead of 4. Any way around this in formulas?

...

=SUMPRODUCT(LEN(X)-LEN(SUBSTITUTE(X,"reject","")))/LEN("reject")

where X must be a single area range or array.

 

[Sally]

Thank you for your help
I used the formula you suggested and I'm getting the
infamous message "The formula you typed contains an error"
It then highlights the quotation mark (2nd from right)
Any suggestions?

 

[Dave]

Hi Sally; I got this formula to work fine-- but I will break it down
so you know what components it needs;

anything in "" is going to be written on the screen like that. So "("
just appears as a ( in the formula result. This formula is simple, it
uses the countif function other people wrote about --
countif(a1:a10,"*reject*") gives you the value you are looking for.
The rest is just TEXT (i.e. anything between quotes) to get it to look
the way you've requested. Any text you add in this way needs to have a
'&' in front of it unless it starts the formula (as the one I gave you
shows-- there is no & in front of the FIRST quotes, but there is in
front of other quotes and the function itself).

If you can get =countif(a1:a10, "*reject*") to return the number,
start with that. Then just add the TEXT around the formula

="(" & {the countif formula} &") Reject"

-- make sure you are pasting this in the formula bar and not right on
the cell, and delete any spaces that may appear after it. it should be
on 1 line.

-- written again---

="("&Countif(a1:a10,"*reject*")&") Reject"

Just make sure there are an even number of quotes in the formula, and
there shouldnt be a problem.

 

[Sally]

Thank you so much for your help - That works great - Can
the formula be altered so it returns the total in
parenthesis? Example: (10) Reject

Thank you again

 

[Anon]

One way would be to put a minus sign between the equals sign and SUMPRODUCT:
=-SUMPRODUCT(LEN(X)-LEN(SUBSTITUTE(X,"reject","")))/LEN("reject")
and format the cell to show negative values in parentheses.

 

[Sally]

Thank you for your help
I used the formula you suggested and I'm getting the
#VALUE! error - Any suggestions?

 

[Dave]

f*cking internal service errors.

you can put any text you want around the value returned by a formula
by reading my message where I broke the formula down for you. if you
want to use Harlan's formula, it would look like this

="("&[harlans formula]&") Reject"

or else just use the one I typed in for you on my earlier message,
because it does exactly as you want.