count comma separated information

you can split the comma seperated values into seperatecolumn using the "text to columns" function on the data tab

then use countif to get the counts.

Hi,

Probably the easiest approach would be as follows,

Use Data Text To columns to separate the strings into several columns.
Copy each column underneath the first column to create a long list and then use Data Filter Advanced Filter to extract a unique list of Server references.

Finally in a column adjacent to the unique list which starts in say E1, and where the long list is in column A, say cells A1:A100, in F1 enter

=COUNTIF(A$1:A$100,E1)

and copy this down the unique list rows.

HTH

you arn't making it very clear why not! post a sample sheet.

That doesn't explain why you can't use text to columns

Based on the sample:

E18:
=SUMPRODUCT(--ISNUMBER(SEARCH(" "&REPLACE($D18,1,19,"")&","," "&$B$2:$B$5&",")))
copied down

Use of SUMPRODUCT is advised here as you may otherwise be open to false positives (ie normalise all terms per consistent delimiter), ie:

=COUNTIF($B$2:$B$5,"*"&REPLACE($D18,1,19,"")&"*")

would work for the sample but not if you changed one of the server 1 instances to say Server 11.

=COUNTIFS(B2:B5,"=*" & C12& "*")

where C12 is "Server 1"

eg

=COUNTIFS(B2:B5,"=*Server 1*")

Tony, the COUNTIF has already been illustrated (and highlighted as being susceptible to error) - not sure what this latest post is adding ?

(also, use of equals operator not really required)

Sorry

Originally Posted by DonkeyOte

Tony, the COUNTIF has already been illustrated (and highlighted as being susceptible to error) - not sure what this latest post is adding ?

wise man say: when broadband down don't just post without checking for updates

I get used to another forum where they warn you that there has been an intervening post before allowing you to commit your reply.

could try
=SUMPRODUCT(--(ISNUMBER(SEARCH("r 3,",TRIM($B$2:$B$5))))) note the space between r and 3

My point holds Martin that when dealing with delimited strings you should always normalise the strings by appending with delimiter (even if initially the terms appear mutually exclusive - this may not persist)

Consider

Server 1, Server 3, Server 4
Server 1, Server 2, Server 3

and we're looking for Server 3, using the last suggestion the result would be 1 rather than the correct value of 2 (the 2nd instance of Server 3 is not found given it is not followed by comma).

To get the correct value we must append each string with the delimiter (ie add the comma to the end of each string so that the final term is consistent with those preceding it).

If you opt not to normalise the strings there is no need to use SUMPRODUCT given a standard COUNTIF with wildcard would suffice, ie:

=COUNTIF(B2:B5,"*r 3,*")

would generate the same [albeit incorrect] result of 1


The end result of all of the above is that I stand by my earlier suggestion of:

=SUMPRODUCT(--ISNUMBER(SEARCH(" "&REPLACE($D18,1,19,"")&","," "&$B$2:$B$5&",")))

In the above each string being searched (and criteria value) is appended such that first term is preceded by space and final term is followed by comma (ie all terms have consistent delimiter pattern)

what about ?
=SUMPRODUCT(--(ISNUMBER(SEARCH("r 3,",TRIM($B$2:$B$5&",")))))

Possibly, in terms of replacing your original.
However, I see no value in reducing the term from "Server" to "r" given again this just adds to the possibility of false positives and by appending the strings that is what we're trying to avoid.

This thread appears to have gone full circle - ie we're posting solutions that were posted half a dozen posts ago ...

ok fair point on "server"
=SUMPRODUCT(--ISNUMBER(SEARCH(" "&REPLACE($D18,1,19,"")&","," "&$B$2:$B$5&",")))
=SUMPRODUCT(--(ISNUMBER(SEARCH("server 3,",TRIM($B$2:$B$5&",")))))
look like different solutions to me.

Originally Posted by mdw

...look like different solutions to me.

Yes, the former is based on OP file and is designed such that it can be copied down without requiring alteration.
(of course if the terms in D18 onwards reflected the terms being searched for the REPLACE could be dispensed with)

Note also that the space acts as a delimiter also (front end) "server 3," is still open to false positives (theoretically at least)

i had another look and still cant see why text to columns isn't doable,seeing as you can only have one instance of any server per customer(assumption )
then when split over several columns and cleaned up just a simple
=COUNTIF(B:D,"server 1")

@FunkyFox - though not related to the question I would suggest that rather than assume a default of +1 it is perhaps better to use a +(D28<>"")
this would account for blank cells should the formula be applied to such (ie return 0 rather than 1).

@mdw et al - the use of Text to Columns is based on some pretty fundamental assumptions.

Given we're just reiterating posts already made I won't add further - so disregard my points as seen fit.