CODEWITHSUNDEEP
excelconditional-formatting
SamC
SamC

Reputation: 47

Excel: Conditional formatting using COUNTIFS and AND

I'm trying to highlight values based on three criteria.

I have two sheets: one that has my detail (OVERVIEW) and one that I'm using as reference (LOOKUP).

**OVERVIEW (Sheet1)**
Column A contains DAY
Column B contains ACTIVITIES
If Monday is selected in column A choices are limited to ICT, Art or Music in column B

**LOOKUP (Sheet2)**
Column A contains DAYS
Column B contains ACTIVITIES
Column C contains PLACES

    A       B       C
1 Monday    ICT     6
2 Monday    Art     8
3 Monday    Music   16
4 Tuesday   Art     12
5 Tuesday   Science 10
3 Wednesday ICT     6
4 Wednesday Science 5

I've used Format Table for the LOOKUP sheet that uses an INDIRECT function so that when I use the OVERVIEW sheet I can choose DAY in column A and the relevant activity in column B

Is there a formula I can use for my OVERVIEW sheet that will highlight rows if they exceed criteria from my LOOKUP sheet?

i.e. [based on the example] If I have 7 (column C) entries that have both Monday (column A) and ICT (column B) then it will highlight the row.

I think I need to use a combination of COUNTIFS and AND but I'm unsure how to amalgamate them. Hope this makes sense to someone.

Thanx for looking, Sam

Upvotes: 2

Views: 8178

Answers (2)

pnuts
pnuts

Reputation: 59475

I have a different interpretation of the requirement. Please clear all conditional formatting (from OVERVIEW), select as many of the columns there as constitute your row width and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=COUNTIFS(LOOKUP!$A:$A,$A1,LOOKUP!$B:$B,$B1)>INDEX(LOOKUP!$C:$C,MATCH($A1&"|"&$B1,LOOKUP!$E:$E,0))

Format..., select highlighting of choice OK, OK.

For the above to work (and I suspect one of us is making this much more complicated than really necessary) a helper column is required in LOOKUP, which I have above as ColumnE populated with:

 =A1&"|"&B1

copied down to suit.

Upvotes: 1

ashleedawg
ashleedawg

Reputation: 21639

You can use COUNTIFS in the Conditional Formatting dialog. AND is not needed since COUNTIFS will count the number of rows that match multiple criteria.

With your example, if you wanted to count rows where both:

  • Column A="Monday" and Column B="ICT"

...then the formula would be:

=COUNTIFS(A:A,"Monday",B:B,"ICT")

If you want Sheet 2, Row 1 to be highlighted based on the count of rows on Sheet 1 where both Column A=Sheet 2, A1 and Column B=Sheet2, B1, when the row count exceeds the number in Sheet2, C1...

  1. On Sheet 2, click the heading for Row 1.
  2. On the Home tab, click Conditional Formatting, then New Rule.
  3. Click Use a formula to determine which cells to format.

  4. In the Format cells where this formula is true box, enter or paste formula:

    =COUNTIFS(Sheet1!$A:$A,Sheet2!$A$1,Sheet1!$B:$B,Sheet2!$B$1)>Sheet2!$C$1

  5. Next click Format.

  6. Go to the Fill tab and choose a colour and/or pattern.
  7. Click OK and OK again.

More Information:

Upvotes: -1

Related Questions