Forum Discussion
I'm trying to count records between a start and finish date, with another text based criteria
I'm trying to count records between a start and finish date, based, with another text based criteria. I have a series of annual contracts with a start and finish date. I want to be able to select an...
You can use the following formula in Excel to count records between a start and finish date with another text based criteria:
=SUMPRODUCT((Start_Date<=Date)*(Finish_Date>=Date)*(Criteria=Text))
Where:
- Start_Date is the range of cells containing the start dates of your annual contracts
- Finish_Date is the range of cells containing the finish dates of your annual contracts
- Criteria is the range of cells containing your text based criteria
- Text is the text you want to match in your criteria
- Date is the nominated date you want to select
You can also modify this formula to count by contract type for a given date by adding an additional condition to it.
I hope this helps!
I know I don't know anything (Socrates)