# Counting Value Based on Date in another Sheet

1. ## 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

2. ## Re: Counting Value Based on Date in another Sheet

You can use:

=COUNTIFS(Holidays!A:A,">="&\$B\$1,Holidays!A:A,"<="&EOMONTH(\$B\$1,0))

Note that B1 is ciurrently set to 1/2/21.... and the formula will retun 0.

3. ## Re: Counting Value Based on Date in another Sheet

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.

4. ## Re: Counting Value Based on Date in another Sheet

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

5. ## Re: Counting Value Based on Date in another Sheet

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

6. ## Re: Counting Value Based on Date in another Sheet

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.

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

7. ## Re: Counting Value Based on Date in another Sheet

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

8. ## Re: Counting Value Based on Date in another Sheet

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

9. ## Re: Counting Value Based on Date in another Sheet

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

10. ## Re: Counting Value Based on Date in another Sheet

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

11. ## Re: Counting Value Based on Date in another Sheet

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

12. ## Re: Counting Value Based on Date in another Sheet

Originally Posted by grcshekar
Changed the month to Apr 21. Though there is only one date in holiday list, AI returns 2
That formula is for AI, if AI is holiday count.
In AH, count "P" regardless holiday:
=COUNTIFS(\$C\$3:\$AG\$3,"<>",\$C4:\$AG4,"P")

13. ## Re: Counting Value Based on Date in another Sheet

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

There are currently 1 users browsing this thread. (0 members and 1 guests)