If function for dated range

[shantraj.antin@gmail.com]

Hi All,

From the attached file I am trying to find the total count of present in Cell D3 based on the date available in D2 comparing the Date range from E6 to EI6. I need only the present count alone for the respective date in D2. I tried to use the below formula but it couldn't work.

COUNTIFS($G$6:$AK$6,"="&$D$2,COUNT("P"))

Thanks in advance!

Regards,
Shan

 

[Jake Collins]

Dear Shan

This formula counts the number of "P"s in the column which match the date in D2. In your spreadsheet this gives an answer of 5 for the date 10-jul-14. I don't understand how you got an expected answer of 5.70.

=COUNTIF(OFFSET(E7,0,MATCH(D2,E6:AI6,0)-1,6,1),"P")

 

[shantraj.antin@gmail.com]

Hi Jake,

Thanks and Appreciate your quick support.

Your formula is working fine for me.

5.70 is expected 95% of head counts.

Regards,
Shan