Count values based on cell color (conditionally formatted)

[Pradeep D]

Hi Chandoo,

Got a question. I've to enter the attendance of an employee from col N to Col AR. I've col headers as dates (1 to 31) and rows have the month (in number). I've made a conditional formatting to highlight cells which are falling on weekends. My aim to track how many employees are working on weekends so that I can give them off on weekends in the next month. I want to track number of sat's Present in col L and sun's Present in col M. As this is conditional formatting, I am unable to count it based on cell color. Please help or recommend in case if you have a better solution.

I've attached a sample sheet with sample data.

Thanks and regards,
Pradeep D

 

[eibi]

Using the same basic framework that you already have in your conditional formatting formula:

In cell L3:

=SUMPRODUCT((TEXT(DATE(2016,A3,$N$2:$AR$2),"ddd")="Sat")*($N3:$AR3="P"))

In cell M3:

=SUMPRODUCT((TEXT(DATE(2016,A3,$N$2:$AR$2),"ddd")="Sun")*($N3:$AR3="P"))

Drag to fill.

 

[bosco_yip]

Another option :

In cell L3 copy across to M3 and all copy down :

=SUMPRODUCT((TEXT($N$2:$AR$2&"/"&$A3,"ddd")=LEFT(L$2,3))*($N3:$AR3="P"))

or,

=SUMPRODUCT((MOD($N$2:$AR$2&"/"&$A3,7)=COLUMN(A1)-1)*($N3:$AR3="P"))

Regards

 

[Pradeep D]

Thank you eibi.. It worked.. was banging my head since a week

Thanks and regards,
Pradeep D

Using the same basic framework that you already have in your conditional formatting formula:

In cell L3:

=SUMPRODUCT((TEXT(DATE(2016,A3,$N$2:$AR$2),"ddd")="Sat")*($N3:$AR3="P"))

In cell M3:

=SUMPRODUCT((TEXT(DATE(2016,A3,$N$2:$AR$2),"ddd")="Sun")*($N3:$AR3="P"))

Drag to fill.

 

[Pradeep D]

Ty bosco_yip

Another option :

In cell L3 copy across to M3 and all copy down :

=SUMPRODUCT((TEXT($N$2:$AR$2&"/"&$A3,"ddd")=LEFT(L$2,3))*($N3:$AR3="P"))

or,

=SUMPRODUCT((MOD($N$2:$AR$2&"/"&$A3,7)=COLUMN(A1)-1)*($N3:$AR3="P"))

Regards