How to countif to partial text

[ks100]

Is there a way to do a COUNTIF on just partial text?

I know how to count how many cells in a column contain the text "cars" for example, but how would i write a formula that include cells with other text as well?

For example if 55 cells have "cars" as a value
and 20 cells have "fast cars" as a value

I would want the formula to count 75.

Basically, it needs to count how many cells cars appears in, in a specific column, regardless of whether its alone in the cell or part of a sentence.

Thanks!

[ConneXionLost]

Try:

Please Login or Register  to view this content.

[ks100]

Try:

Please Login or Register  to view this content.

Thanks, now how would I incorporate that to instead of referring to all of column A, refer to all of column (INDEX MATCH "Vehicles") ?

So basically, it is finding the "Vehicles" column, wherever that may be on the spreadsheet, and performing the count.

[ConneXionLost]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Tony Valko]

Something like this...

=COUNTIF(INDEX(A:D,0,MATCH("Vehicles",A1:D1,0)),"*Cars*")

Where Vehicles is a column header somewhere in the range A1:D1.

[ks100]

Something like this...

=COUNTIF(INDEX(A:D,0,MATCH("Vehicles",A1:D1,0)),"*Cars*")

Where Vehicles is a column header somewhere in the range A1:D1.

Got it, what if the headers are some times in row 3 or 4? What would the range become, A1:D4?

[Tony Valko]

I think it's time to post a sample file so we can see what we're dealing with.

[ks100]

I think it's time to post a sample file so we can see what we're dealing with.

Ok updated with a sample. Each sheet of the sample shows a possible location for the header. The code you gave me worked Tony, however I am looking to modify it to detect if the header is as far down as row 4.

Thanks

[Tony Valko]

Well, that makes it a lot more complicated.

Assuming the column header "Vehicles" will be within the range A1:H4.

Try this array formula**:

=COUNTIF(OFFSET(INDIRECT(ADDRESS(MAX(IF(A1:H4="Vehicles",ROW(A1:H4))),MAX(IF(A1:H4="Vehicles",COLUMN(A1:H4))),4)),0,0,100),"*Cars*")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Change the 100 to reflect the number or rows of data that you think you will have or need.

[xwarlock10x]

=SUMPRODUCT(--ISNUMBER(SEARCH({cell where the search name is },A1:A100)))

and the a1:100 is the cells where it is looking the partial name at

look at example sheet 1

[ks100]

Well, that makes it a lot more complicated.

Assuming the column header "Vehicles" will be within the range A1:H4.

Try this array formula**:

=COUNTIF(OFFSET(INDIRECT(ADDRESS(MAX(IF(A1:H4="Vehicles",ROW(A1:H4))),MAX(IF(A1:H4="Vehicles",COLUMN(A1:H4))),4)),0,0,100),"*Cars*")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Change the 100 to reflect the number or rows of data that you think you will have or need.

It works, however with an odd glitch. If the actual array formula is posted anywhere in the rows its "searching" it forms a circular reference and won't calculate. To remedy this, I am going to try:

Please Login or Register  to view this content.

And insert the array in the A column. I will see if that works.

[Tony Valko]

Yes, you would have to put the formula outside of any ranges it refers to.