Formula to count cells with conditional formatting

[Ayman Abid]

Hi there,

I created a calendar in excel where periods of dates are highlighted with a formula in conditional formatting. Are we able to count the dates highlighted using say a countif or sumif formula? I would prefer not using VBA. Many thanks for your help in advance. I have also attached a copy of my file for your reference. Best regards.

[#2]

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.

[#3 · Accepted solution]

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

[#4]

Many thanks to both Arul and Sergei for your kind response to my inquiry. I really appreciate it :).

Sergei thank you for providing me with the formulas below that is exactly what i needed.

I wish you both the best.

Best regards,