COUNT IF... I think! XL2000

[AnderE]

Hi,

I have a list of 100 codes (eg C18EA) and I am trying to count how many times each code occurs in several other lists, eg H1:H26014

So far I have the following:

=COUNTIF(H$1:H$26014,"*C18EA*")

But doing it this way I have to do each code individually or at least add the correct ocde into each formula.

Say C18EA is in cell A1, is there any way that I can tell excel to count the number of times A1 is found in the list H1:H26014? I have tried the following, but it doesn't work - it tells me I have too few arguments...

=COUNTIF(H$1:H$26014=A1)

I hope this makes sense!

Any help appreciated.

 

[Jonmo1]

Try
=COUNTIF(H$1:H$26014,"*" & A1 & "*")

 

[VoG]

Try

=COUNTIF(H$1:H$26014,"*"&A1&"*")

 

[healey21]

Try this out,

Select a cell in the table, then look to sort A to Z then use the Data Tab and look at subtotals, you can set the subtotal to count based on your code column.

 

[AnderE]

Thankyou to you both! Worked a treat!

I had also tried ,"*A1*" and ,"*"&A1 but obviously neither worked....

Thankyou so much!