Formula that checks if a certain date is in a date range and TRUE/FALSE VALUES
Hello!
I need help on a formula that outputs a count and a sum since we are unable to build formulas from a gantt chart.
This is the formula page I am using in order to build a gantt chart with a bar chart in a dashboard with month counts and sums.
I created this report to show what is needed in the formula.
Count:
Count of location for the first month of April 2020 = check to see if April 2020 (column 8) is a month within Repayment Period Start and Repayment End. If yes, count it towards the April 2020 count of locations. But also making sure it looks at everything in the "What" filter.
SUM:
If yes above, add the amount to the additional $ amount column.
The months in column 8 go all the way until 2026.
Thank you in advance!
Answers

For the count column, you can try to use a COUNTIFS() formula.
Psuedo code for [column9]1 would be something like:
=COUNTIFS( RepaymentPeriodStartColumnExternalRef, [column8]1 >= @cell, ReplaymentPeriodEndColumnExternalRef, [Column8]1 <= @cell)
* you will need to make sure I think that the [column8]1 is in a date format to do the comparisons
** you will need to create an external reference that points to period start and end dates unless this is all being done in the same table
Psuedocode for the [column10]1 would be exactly the same logic but with a different funtion...
=SUMIFS( RepaymentPeriodStartColumnExternalRef, [column8]1 >= @cell, ReplaymentPeriodEndColumnExternalRef, [Column8]1 <= @celll)
* again, you will need to make sure I think that the [column8]1 is in a date format to do the comparisons
Drag the formula down into the other cells.
Help Article Resources
Categories
Check out the Formula Handbook template!