Forum Discussion
Formula to count cells with conditional formatting
I'd suggest to simplify a bit condutional formatting rule and use formula for it like
=COUNTIFS($AA$6:$AA$277,"<="&B7,$AB$6:$AB$277,">="&B7)
To count the nubmer of highlighted days
=SUMPRODUCT(($AB$6:$AB$277-$AA$6:$AA$277+1)*ISBLANK($AB$6:$AB$277)*ISBLANK($AA$6:$AA$277))
Please see attached
Hi there,
if the highlighted cells are linked to the dates you insert in column AA and AB, you can count "how many highlights are visible" by simply counting how many days the dates are counting; "end date - start date +1" can count how many days are on each line on Z column, then you add a counter of the counters ("=sum(AD:AD)") and that's the number of highlighted cells you have. This works IF and only IF you have unique dates duties: if you insert 12/8 to 12/20 in line 1 and 12/12 to 12/25 in line 2, the formula I just told is not working (it will sum 13 + 14 instead of 26 total).
To avoid this problem (if you have it), you have to scompose dates with some ranking formulas and lots of columns depending on how many lines (column Z) you need.