Google Sheets, Using Conditional Formatting with COUNITIF and REGEXTRACT need partial matches The Next CEO of Stack OverflowHow to count cells in range, with multiple criteria OR how to filter range for countif/counta/subtotal etcCOUNTIF function if day of week is TuesdayGoogle Sheets COUNTIF / COUNTIFS formulas based on multiple criteriaCount multiple columns if they contain specific text using the counta functionConditional Formatting cells based on how many times it appears in separate Columniterate row by row COUNTIF using ArrayFormula on Google SheetsLibreOffice Calc — countif based on string lengthExcel COUNTIF with conditional formattingWith Excel Conditional Formatting - Want to Highlight Cells in a range that match any from another range of cellsExcel conditional formatting with CountIf
Reference request: Grassmannian and Plucker coordinates in type B, C, D
Airplane gently rocking its wings during whole flight
What would be the main consequences for a country leaving the WTO?
free fall ellipse or parabola?
How to avoid supervisors with prejudiced views?
Does destroying a Lich's phylactery destroy the soul within it?
Is it ok to trim down a tube patch?
What does "shotgun unity" refer to here in this sentence?
Computationally populating tables with probability data
Is it professional to write unrelated content in an almost-empty email?
From jafe to El-Guest
Graph of the history of databases
TikZ: How to fill area with a special pattern?
Redefining symbol midway through a document
Getting Stale Gas Out of a Gas Tank w/out Dropping the Tank
Yu-Gi-Oh cards in Python 3
How do I fit a non linear curve?
Are the names of these months realistic?
Would a grinding machine be a simple and workable propulsion system for an interplanetary spacecraft?
Can I calculate next year's exemptions based on this year's refund/amount owed?
what's the use of '% to gdp' type of variables?
Is French Guiana a (hard) EU border?
How did Beeri the Hittite come up with naming his daughter Yehudit?
Can I board the first leg of the flight without having final country's visa?
Google Sheets, Using Conditional Formatting with COUNITIF and REGEXTRACT need partial matches
The Next CEO of Stack OverflowHow to count cells in range, with multiple criteria OR how to filter range for countif/counta/subtotal etcCOUNTIF function if day of week is TuesdayGoogle Sheets COUNTIF / COUNTIFS formulas based on multiple criteriaCount multiple columns if they contain specific text using the counta functionConditional Formatting cells based on how many times it appears in separate Columniterate row by row COUNTIF using ArrayFormula on Google SheetsLibreOffice Calc — countif based on string lengthExcel COUNTIF with conditional formattingWith Excel Conditional Formatting - Want to Highlight Cells in a range that match any from another range of cellsExcel conditional formatting with CountIf
I am trying to use a COUNTIF in conditional formatting matching partial strings but I am using REGEXEXTRACT to get the partial strings and matching them to cells that contain strings and numbers.
What is the Syntax for "*TEXT*" (Which allows partial matches normally in a countif). but replacing "*TEXT*" with a formula REGEXEXTRACT(C1,"([A-Za-z- ]+)")
Current Conditional formatting Works if the text is exactly matched in another cell in the range. Need syntax for partial matches of the extracted text.
=ARRAYFORMULA(COUNTIF($C$1:$Z$30, REGEXEXTRACT(C1,"([A-Za-z- ]+)")))
https://docs.google.com/spreadsheets/d/1Qnuhmti-hMMOggRnAt8t9xYEr0c_2esqcy8M7pOH16k/edit#gid=0
Sheet 2 is the conditional formatting. Sheet 1 is just data used by sheet 2.
Conditional format desired result is that something like Quality Dodge +4% should highlight red with Quality Dodge +3% The array formula is there because I will also be using regexextract to compare the number values in the countif as well to highlight the lower bonuses.
countif partial-matches
asked Mar 7 at 17:06
Alden TiefenbachAlden Tiefenbach
14
add a comment |
I am trying to use a COUNTIF in conditional formatting matching partial strings but I am using REGEXEXTRACT to get the partial strings and matching them to cells that contain strings and numbers.
What is the Syntax for "*TEXT*" (Which allows partial matches normally in a countif). but replacing "*TEXT*" with a formula REGEXEXTRACT(C1,"([A-Za-z- ]+)")
Current Conditional formatting Works if the text is exactly matched in another cell in the range. Need syntax for partial matches of the extracted text.
=ARRAYFORMULA(COUNTIF($C$1:$Z$30, REGEXEXTRACT(C1,"([A-Za-z- ]+)")))
https://docs.google.com/spreadsheets/d/1Qnuhmti-hMMOggRnAt8t9xYEr0c_2esqcy8M7pOH16k/edit#gid=0
Sheet 2 is the conditional formatting. Sheet 1 is just data used by sheet 2.
Conditional format desired result is that something like Quality Dodge +4% should highlight red with Quality Dodge +3% The array formula is there because I will also be using regexextract to compare the number values in the countif as well to highlight the lower bonuses.
countif partial-matches
asked Mar 7 at 17:06
Alden TiefenbachAlden Tiefenbach
14
Figured it out, had the "*"& in the incorrect places. "*"&(REGEXEXTRACT(C1,"([A-Za-z- ]+)"))&"*" Will partial match like any other string.
– Alden Tiefenbach
Mar 9 at 1:07
add a comment |
I am trying to use a COUNTIF in conditional formatting matching partial strings but I am using REGEXEXTRACT to get the partial strings and matching them to cells that contain strings and numbers.
What is the Syntax for "*TEXT*" (Which allows partial matches normally in a countif). but replacing "*TEXT*" with a formula REGEXEXTRACT(C1,"([A-Za-z- ]+)")
Current Conditional formatting Works if the text is exactly matched in another cell in the range. Need syntax for partial matches of the extracted text.
=ARRAYFORMULA(COUNTIF($C$1:$Z$30, REGEXEXTRACT(C1,"([A-Za-z- ]+)")))
https://docs.google.com/spreadsheets/d/1Qnuhmti-hMMOggRnAt8t9xYEr0c_2esqcy8M7pOH16k/edit#gid=0
Sheet 2 is the conditional formatting. Sheet 1 is just data used by sheet 2.
Conditional format desired result is that something like Quality Dodge +4% should highlight red with Quality Dodge +3% The array formula is there because I will also be using regexextract to compare the number values in the countif as well to highlight the lower bonuses.
countif partial-matches
asked Mar 7 at 17:06
Alden TiefenbachAlden Tiefenbach
14
I am trying to use a COUNTIF in conditional formatting matching partial strings but I am using REGEXEXTRACT to get the partial strings and matching them to cells that contain strings and numbers.
What is the Syntax for "*TEXT*" (Which allows partial matches normally in a countif). but replacing "*TEXT*" with a formula REGEXEXTRACT(C1,"([A-Za-z- ]+)")
Current Conditional formatting Works if the text is exactly matched in another cell in the range. Need syntax for partial matches of the extracted text.
=ARRAYFORMULA(COUNTIF($C$1:$Z$30, REGEXEXTRACT(C1,"([A-Za-z- ]+)")))
https://docs.google.com/spreadsheets/d/1Qnuhmti-hMMOggRnAt8t9xYEr0c_2esqcy8M7pOH16k/edit#gid=0
Sheet 2 is the conditional formatting. Sheet 1 is just data used by sheet 2.
Conditional format desired result is that something like Quality Dodge +4% should highlight red with Quality Dodge +3% The array formula is there because I will also be using regexextract to compare the number values in the countif as well to highlight the lower bonuses.
countif partial-matches
countif partial-matches
asked Mar 7 at 17:06
Alden TiefenbachAlden Tiefenbach
14
asked Mar 7 at 17:06
Alden TiefenbachAlden Tiefenbach
14
edited Mar 9 at 0:07
Alden Tiefenbach
asked Mar 7 at 17:06
Alden TiefenbachAlden Tiefenbach
14
asked Mar 7 at 17:06
Alden TiefenbachAlden Tiefenbach
14
asked Mar 7 at 17:06
Alden TiefenbachAlden Tiefenbach
14
14
Figured it out, had the "*"& in the incorrect places. "*"&(REGEXEXTRACT(C1,"([A-Za-z- ]+)"))&"*" Will partial match like any other string.
– Alden Tiefenbach
Mar 9 at 1:07
add a comment |
Figured it out, had the "*"& in the incorrect places. "*"&(REGEXEXTRACT(C1,"([A-Za-z- ]+)"))&"*" Will partial match like any other string.
– Alden Tiefenbach
Mar 9 at 1:07
Figured it out, had the "*"& in the incorrect places. "*"&(REGEXEXTRACT(C1,"([A-Za-z- ]+)"))&"*" Will partial match like any other string.
– Alden Tiefenbach
Mar 9 at 1:07
Figured it out, had the "*"& in the incorrect places. "*"&(REGEXEXTRACT(C1,"([A-Za-z- ]+)"))&"*" Will partial match like any other string.
– Alden Tiefenbach
Mar 9 at 1:07
add a comment |
0
active
oldest
votes
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55049319%2fgoogle-sheets-using-conditional-formatting-with-counitif-and-regextract-need-pa%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55049319%2fgoogle-sheets-using-conditional-formatting-with-counitif-and-regextract-need-pa%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Figured it out, had the "*"& in the incorrect places. "*"&(REGEXEXTRACT(C1,"([A-Za-z- ]+)"))&"*" Will partial match like any other string.
– Alden Tiefenbach
Mar 9 at 1:07