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?
 
Wayne Taylor (webtubbs)Connect With a Mentor 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
 
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
 
Ryan ChongCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
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 ChongConnect With a Mentor Commented:
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 ChongCommented:
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 ChongCommented:
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 ChongCommented:
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 ChongCommented:
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 ChongCommented:
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 ChongCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.