SOLVED

Count within a date range

Occasional Contributor

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!

 

Ry.JPG

5 Replies

@swignall 

 

=COUNTIF(B2:S2, "D")

 

Hope I was able to help you with this information.

 

NikolinoDE

I know I don't know anything (Socrates)

This wouldnt count between the dates, it would count in total right to the end..
Thanks
best response confirmed by swignall (Occasional Contributor)
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.

@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

 

Thank you Riny, both worked perfectly!