Forum Discussion
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:
- 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)
- Yellow -> If there are atleast two posts in the same day (DD) and month (MM) but in two separate years (YYYY)
- 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_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