# 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?
###### Who is Participating?

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

Building 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

Commented:
alternatively you can also use formula in conditional formatting

for your first case, try use formula:

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

for your second case, try use formula:

``````=COUNTIFS(B2:B2,"<>y",B2:B2,"<>z")=1
``````
29071697.xlsx
0

Building 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

Building 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

Commented:
I try to answer that correctly...

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

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

Commented:
btw, what Wayne's posted should also solved your original questions as well
0

Building 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

Commented:
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

Building EstimatorAuthor Commented:
hi ryan chong, the hilite will apply for the column criteria and for the row criteria
0

Building EstimatorAuthor Commented:
therefore when row 13 and column h criterias are met the cells within the same range will be hilited in yellow
0

Commented:
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
``````

For Cols, you got to use:
``````=COUNTIF(C\$4:C\$13,"*D*")>0
``````
ee-count-formula-with-hilite-cells-.xlsx
0

Building 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

Building 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

Commented:
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

Building EstimatorAuthor Commented:
hi ryan chong, my sample wkbk in post ID: 42390776, please apply the 3 conditional formats you're proposing
0

Commented:
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

Commented:
any further details to be provided here?
0

Building 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.