Formula that checks if a certain date is in a date range and TRUE/FALSE VALUES

Gabriela Colvis

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!

jwilson10533

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.