Counting Value Based on Date in another Sheet

Dear Experts

I have just made a simple attendance Sheet.

Attached sheet has a tab named Holidays and Attendance.
In the attendance, based on date and Month in Holidays I am updating Column AH.
Example, In Jan 2021, there are two days in Holidays. That is 14 and 26. In Attendance tab, if there is value "P" on 14 and 26 I am updating AH with Value 2
Is there anyway to automate this

I changed to Apr 21.
C4:C30 all values blank. It still returns 1 in AI

I would suggest that you change what look like days of the month on row 3 to actual dates, formatted to show days. To do that, use these formulae in the cells stated:

C3: =B1

D3: =C3+1

Copy this across to AD3, as every month has at least 28 days.

AE3: =IF(AD3="","",IF(MONTH(AD3+1)<>MONTH(AD3),"",AD3+1))

Copy this across to AG3, and then apply a Custom Format of d to the cells C3:AG3

Then in AI4 you can use this array* formula:

=SUM(COUNTIFS(C$3:AG$3,Holidays!$A$2:$A$11,C4:AG4,"P"))

which can then be copied down as required.

*Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), instead of the usual Enter.

The attached file shows this in operation.

Hope this helps.

Pete

Originally Posted by Glenn Kennedy

The formula in AH is faulty, too. I suggest you use

=SUMPRODUCT(($C$3:$AG$3<>"")*(C4:AG4="P"))

to avoid th epossibility of counting P cvalues that do not exist in (for example) February, a shorter month.

I changed to Apr 21.
C4:C30 all values blank. It still returns 1 in AI

Originally Posted by bebo021999

First, try to convert row 3 in to date, format as "d"
C3=B1
D3=IF(OR(C3="",C3>=EOMONTH($B$1,0)),"",C3+1)
Drag accross
AH4 count "P" fall into holiday:

Please Login or Register  to view this content.

=2

Changed the month to Apr 21. Though there is only one date in holiday list, AI returns 2

Originally Posted by Glenn Kennedy

That would be because there is a holiday in April!!!!

Date 13 I did not enter value. Date 13 is holiday since there is no value P in date 13 it should not return value

Originally Posted by Glenn Kennedy

The formula in AH is faulty, too. I suggest you use

=SUMPRODUCT(($C$3:$AG$3<>"")*(C4:AG4="P"))

to avoid th epossibility of counting P cvalues that do not exist in (for example) February, a shorter month.

If there is P in 13 only then it should count

Originally Posted by bebo021999

That formula is for AI, if AI is holiday count.
In AH, count "P" regardless holiday:
=COUNTIFS($C$3:$AG$3,"<>",$C4:$AG4,"P")

Thank You Very Much