Mar 21 2023 05:29 PM
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
Mar 22 2023 12:40 AM
=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?
Mar 22 2023 12:44 AM
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:
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)