COUNT FORMULA WITH HILITE CELLS

hi experts, how can I building into the following countif formula a cell hilite function,

1:=COUNTIF(A2:A15,"*D*")

so basically all the cells that meet the above formula criteria to be hilited in the excel wksheet with a background fill color?
Frank .SBuilding EstimatorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Frank .SBuilding EstimatorAuthor Commented:
another example would be to hilite all the cell values that meet the following formula criteria

1:=COUNTIFS(B2:B5, "<>y",B2:B5, "<>z")
0
Wayne Taylor (webtubbs)Commented:
Two different scenarios so different methods apply...

First scenario
1. Select cells A2:A15
2. From the Home tab, select Conditional Formatting > Highlight Cell Rules > Text that Contains.
3. Enter "d"
4. Select from the list of predefined highlights, or choose Custom format to create your own.
5. Click OK

Second scenario
1. Select cells B2:B5
2. From the Home tab, select Conditional Formatting > New Rule
3. Select "Use a formula to determine which cells to format"
4. Enter the formula =AND(B2<>"y", B2<>"z")
5. Click the Format button and select your desired "highlight"
6. Click OK
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
alternatively you can also use formula in conditional formatting

for your first case, try use formula:

=COUNTIF(A2:A2,"*D*")=1

Open in new window


for your second case, try use formula:

=COUNTIFS(B2:B2,"<>y",B2:B2,"<>z")=1

Open in new window

29071697.xlsx
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Frank .SBuilding EstimatorAuthor Commented:
hi ryan chong, your conditional formatting formulas work however the result to the numbers found to each of the formulas on your attached worksheet example? does that mean the conditional formatting formulas are added under the conditional formatting area, and the total number found of each must be shown in the worksheet cell/s?
0
Frank .SBuilding EstimatorAuthor Commented:
similarly what if instead of showing 1 cell background fill color if it meets the criteria, could you show multiple cells or an entire row to be hilited if the criteria is met?
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
I try to answer that correctly...

does that mean the conditional formatting formulas are added under the conditional formatting area,
yes.

Untitled.png
and the total number found of each must be shown in the worksheet cell/s?
do you mean putting similar formula in the worksheet's cell? that's actually not necessary as you can see in the sample, there's no cell placing with a formula with countif.

similarly what if instead of showing 1 cell background fill color if it meets the criteria, could you show multiple cells or an entire row to be hilited if the criteria is met?
yes, you can. just copy the Format Painter to do that... but we need to change formula in conditional formatting a bit, like:

=COUNTIF($A2:$A2,"*D*")=1
and:
=COUNTIFS($B2:$B2,"<>y",$B2:$B2,"<>z")=1

I have uploaded another sample for illustration.
29071697_b.xlsx
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
btw, what Wayne's posted should also solved your original questions as well
0
Frank .SBuilding EstimatorAuthor Commented:
hi experts, could you apply your conditional formats & cell hilites to my attached test wksht?
ee-count-formula-with-hilite-cells-.xlsx
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you got 2 conditional formatting, how you want to highlight the rows that matched the criteria?

which conditional formatting should be used for highlighting?
0
Frank .SBuilding EstimatorAuthor Commented:
hi ryan chong, the hilite will apply for the column criteria and for the row criteria
0
Frank .SBuilding EstimatorAuthor Commented:
therefore when row 13 and column h criterias are met the cells within the same range will be hilited in yellow
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
ok... kinda understand your requirement by adjusting the conditional formatting's formula again.

For Rows, you got to use:
=COUNTIFS($C4:$G4,"<>y",$C4:$G4,"<>z")>0

Open in new window


For Cols, you got to use:
=COUNTIF(C$4:C$13,"*D*")>0

Open in new window

ee-count-formula-with-hilite-cells-.xlsx
0
Frank .SBuilding EstimatorAuthor Commented:
hi ryan chong, are these formulas put into the conditional formatting or into the cell itself?
- also, there are going to be instances whereby using the row and column formulas, they may overlap as in the same cell/s may be hilited for both the column and row formula, so therefor can a background color be the conditional format of say the column formula condition and a cell border for the row condition formula, so if any cell meets both criterias i can see that it will have a background fill color and border that meets both formula criterias?
0
Frank .SBuilding EstimatorAuthor Commented:
hi ryan chong, in your last workbook attachment, you havent shown any cell background fill colors that meet the formula criterias for column and row. I need the range of cells C4:G13 to show background fill colors if they meet any of the 2 conditional formatting criterias.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
so if any cell meets both criterias i can see that it will have a background fill color and border that meets both formula criterias?
if you would like to do that, we need to introduce a third conditional formatting to handle that.

if the sample provided in Comment ID: 42391926 doesn't really help, pls provide an expected sample so that it's easier for reference and provide a solution.
0
Frank .SBuilding EstimatorAuthor Commented:
hi ryan chong, my sample wkbk in post ID: 42390776, please apply the 3 conditional formats you're proposing
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
did some research but i got something need clarify with..

so do you want to highlight the rows/cols or the "results"?

as mentioned, pls provide a sample for better illustration, which include the 3rd rule.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
any further details to be provided here?
0
Frank .SBuilding EstimatorAuthor Commented:
hi experts, the results is what i would like to be shown as hilited
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.