I'm trying to count records between a start and finish date, with another text based criteria

[Vikki230]

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 any nominated date, and know at that point in time how many annual contracts were 'valid'. And, because I have four different types of contracts, I want to also be able to count by contract type for the given date. This is all so I can see a trend in time of growth or decline in customers taking up contracts.

I've tried sumproduct and countifs, but I just can't get anything to work. Sumproduct will let me count between two dates but won't let me include the contract type, and countifs returns 0.  

Pics below are 1) The table I want to populate, 2) What I could do with sumproduct, 3) sample data

[#2]

@Vikki230 

=SUMPRODUCT((B$1=Sheet1!$C$2:$C$24)*(Sheet1!$A$2:$A$24<=Sheet2!$A2)*(Sheet1!$B$2:$B$24>=Sheet2!$A2)*(Sheet1!$D$2:$D$24="valid"))

Does this return the expected result?

 

[#3]

@Vikki230 

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!

NikolinoDE

I know I don't know anything (Socrates)