Countif value in cell is found in another range whilst two sets of dates also match..

Hi,

I've tried but I cant get my brain round this.......Im sure the solution is fairly easy but *sigh*!

I need to count the number of times a value in A2 appears in F2:F20, as well as the dates (on the same row) in J2:J20 and K2:K20 fall between the dates in E2 and F2.

I've attached an example sheet, hopefully it will be obvious when you see it

The answer in the example sheet would be 2!

test.xlsx

Hi,

Try the following in D2:

=COUNTIFS(F$1:F$4,A2,G$1:G$4,">="&B2,H$1:H$4,"<="&C2)

OR,

In D2:

=SUMPRODUCT((F$1:F$4=A2)*(G$1:G$4>=B2)*(H$1:H$4<=C2))

Thanks! That's great. It's fair to say I would never have worked that out

You are welcome

If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.

Also, you can directly thank those who have helped you by clicking on the small * (star) icon located in the lower left corner of a post that you have found to be helpful.