SOLVED

# Count within a date range

Occasional Contributor

# Count within a date range

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!

5 Replies

# Re: Count within a date range

=COUNTIF(B2:S2, "D")

NikolinoDE

I know I don't know anything (Socrates)

# Re: Count within a date range

This wouldnt count between the dates, it would count in total right to the end..
Thanks
best response confirmed by swignall (Occasional Contributor)
Solution

# Re: Count within a date range

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.

# Re: Count within a date range

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

# Re: Count within a date range

Thank you Riny, both worked perfectly!