Count within a date range

[swignall]

I need a formula to show the amount times D and N show in the table between the start and end date. 

For example Sam Son would equal 5 and Joe would equal 16 

Thank you!

[#2]

@swignall 

=COUNTIF(B2:S2, "D")

Hope I was able to help you with this information.

NikolinoDE

I know I don't know anything (Socrates)

[#3]

This wouldnt count between the dates, it would count in total right to the end..
Thanks

[#4 · Accepted solution]

@swignall Please see attached file.

Used some named ranges to avoid direct cell references. Two possible solutions.

One with SUMPRODUCT

=SUMPRODUCT(
    (
        ((INDEX(data,MATCH(A6,empl,0),))="D")+((INDEX(data,MATCH(A6,empl,0),))="N")
    ),
        --(dates>=B6),
        --(dates<=C6)
)

 another with SUM and COUNTIFS

=SUM(
    COUNTIFS(
        dates,  ">="&B6,
        dates,  "<="&C6,
        INDEX(data,MATCH(A6,empl,0),),  {"D","N"}
    )
)

See which one works best for you.

[#5]

@swignall 

Sry was again hasty and didn't read all the way through :)

Here's the formula, untested but should work :)

=SUMPRODUCT((B1:S1>=B6)*(B1:S1<=C6)*(B2:S2="D"))

In hindsight, I recommend Mr. Riny_van_Eekelen formulas, they are more elegant and I think they suit your project better.

NikolinoDE

[#6]

Thank you Riny, both worked perfectly!