COUNTIF (complex) - Google Sheets

[rlong98]

Hello,

I'm puzzled on this one

I have a formula in Conditional Formatting

=NOT(ISERROR(FIND(LOWER(A5),LOWER($B$1))))

This works when I type in a phrase in B1 and it will Highlight all that have some or more of that phrase

GOAL - I want to use that same code in an COUNTIF

=COUNTIF(A5:A30,NOT(ISERROR(FIND(LOWER(A4),LOWER($B$1)))))

I would think that if the first code line works in Conditional Format and highlights the cells, that I could use it in a COUNTIF



I keep getting 0

Thank you!

 

[jasonb75]

The conditional formatting formula returns either TRUE or FALSE, so the COUNTIF formula is counting cells that contain TRUE or FALSE, not cells that are comparable to the content of A4 or B1.

FYI if you use SEARCH instead of FIND then you don't need to convert to lower case to compare.

GOAL - I want to use that same code in an COUNTIF

that is not a goal, it is an impossible task, you might be able to achieve similar with sumproduct instead of countif, but without some indication as to what you're trying to do instead of how you're trying to do it we have little chance of guessing what you actually need.

 

[mole999]

@rlong98 Your post has been reported as being cross posted with another site.

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please provide the link(s)

 

[rlong98]

Thanks Jason
Sorry for the confusion

What I was attempting, is someone already has a workbook when they type in a title in the cell B1, the conditional format highlights all the cells that have the first word or both. So there can be 1:more highlighted.
What I was looking to do is get the total number the conditional format highlighted.
I figured since it was a FIND formula that it would work in a COUNTIF, but it doesn't.

Thank you for the reply

 

[jasonb75]

That will be quite simple to solve, however I will not be doing so until provide the links to your cross posts.

 

[rlong98]

Google Sheets - COUNTIF (complex) [SOLVED]

Here is tough one I have a Conditional Format via Formula =NOT(ISERROR(FIND(LOWER(A4);LOWER($B$1)))) 667794 How it works - when I type if B1 any phrase "Bed Covers" below gets highlighted with some or partial of the text

www.excelforum.com

 

[jasonb75]

=SUMPRODUCT(COUNTIF(B1,"*"&A5:A30&"*")*(A5:A30<>""))

 

[rlong98]

Thank you! Thanks for the Rule Reminder - - I do have brain damage

Do you happen to know why in Google Sheets - ONE Workbook it works perfectly, and in another Workbook it give a Parse Error? As if something is blocking the Function from working.

Found it - (can't delete this msg) - but I still don't know "why"

In the other workbook I had to use semicolon instead of comma
(don't know why)

 

[jasonb75]

In the other workbook I had to use semicolon instead of comma

In excel that is down to regional settings, semicolons are used in countries where decimal numbers use a comma in place of a dot / period. It is based on your system settings, so wouldn't vary between workbooks.
As google sheets is online, it could be based on the origin of the workbook instead, this is just a theory, not something that I know for certain as I have never used google sheets.

 

[rlong98]

Do you happen to know why in Google Sheets - ONE Workbook it works perfectly, and in another Workbook it give a Parse Error? As if something is blocking the Function from working.

Found it - (can't delete this msg) - but I still don't know "why"

In the other workbook I had to use semicolon instead of comma
(don't know why)

FYI for those watching: RE: Google Sheets
When looking at the document - found out the Language Setting was "German" which apparently changes the use of "," to ";" in formulas.
- Why I get Parse Errors still in that document is beyond me - but could be something in the Language concept? When I recreated it in a US / En - everything works