Forum Discussion

eashwarbalaji04's avatar
eashwarbalaji04
Copper Contributor
Aug 28, 2024

Formating a cell in Excel based on multiple conditions

Social Media Posts Analysis:


I have a tabular columns on excel with days (1-31) in columns and the months (1-12) in rows, which looks like this:

The data to this feeds from another sheet in the same excel file. This data is basically saying the date on which a post was made on a social media:

 

 

I have made the tabular column (Image 1) using countifs function, where it automatically takes the day and month from the Date column and places it in appropriate cell in the 1st sheet.

For example:
Sheet1!B2=COUNTIFS(Sheet2!$D:$D,Sheet1!B$1,Sheet2!$E:$E,Sheet1!$A2)


Now my question is:
I want to format the tabular column (Image 1) as follows:

  1. No Background color to cell -> If no posts have been made on this day and month at all or the day (DD) and month (MM) appears only in one year (there can be more than one post in the same date (DD/MM/YYYY), that is not a problem)
  2. Yellow -> If there are atleast two posts in the same day (DD) and month (MM) but in two separate years (YYYY)
  3. Green -> If there are atleast three posts in the same day (DD) and month (MM) but in three separate years (YYYY)

 

I hope that was clear. Please help me out if there are any methods to acheive this formating in Excel.

Let me know if you need any further clarfications.

 

Thanks in advance,

Eashwar

  • Rodrigo_'s avatar
    Rodrigo_
    Steel Contributor

    Hello eashwarbalaji04 

    I have 2 suggestions,
    1. Since we're counting how many shared posts ('Post Number') posted on given dates. It can easily count using a PivotTable based on Sheet 2.

    for conditional formatting:


    2. Using your original set-up on Sheet 1, instead of showing count numbers of Post Number, can it also show the 'Post Number'? like this:

    array formula: =IFERROR(TEXTJOIN(", ", TRUE, IF((Sheet2!$C$2:$C$100=Sheet1!$A$1) * (Sheet2!$D$2:$D$100=B$1) * (Sheet2!$E$2:$E$100=$A2), Sheet2!$A$2:$A$100, "")), "")

     

    yellow conditional formatting (using formula):
    =IF(LEN(B2) - LEN(SUBSTITUTE(B2, ",", "")) + 1 >= 2, TRUE, FALSE)

    green:
    =IF(LEN(B2) - LEN(SUBSTITUTE(B2, ",", "")) + 1 >= 3, TRUE, FALSE)

    here's the sample file > eashwarbalaji04.xlsx

Resources