CODEWITHSUNDEEP
excelexcel-formula
sidgate
sidgate

Reputation: 15244

Can I check values in two columns and all rows to add a conditional value?

Here is what I am trying to do. I am entering a list of leaves in one sheet with To and From date values. Now given a date I want to find out whether there is a leave on that date. How can I do that?

For instance, I add following data in excel

Jack   | 1-Jan-2014  | 3-Jan-2014
Jill   | 15-Jan-2014 | 15-Jan-2014

Now I want to check whether Jill is on leave on 2nd Jan, how can I write a formula to check all the date ranges in two columns?

Now I am now trying to write a custom function to do that. But want to know whether we can do that OOTB.

Upvotes: 1

Views: 70

Answers (1)

maybeWeCouldStealAVan
maybeWeCouldStealAVan

Reputation: 15610

Use COUNTIFS. This:

=COUNTIFS($B:$B,"<="&D2,$C:$C,">="&D2)

will tell you how many leaves contain the date in D2, where leaves start in column B and end in column C.

Upvotes: 3

Related Questions