Filter through date range between two different columns

Hello,

I'm trying to use =countifs to add the number of submissions on a sheet. I've been able to populate the data when I search through one range, but I'm trying to search between two different date columns.

Example:

=COUNTIFS({Planned Completion Date}, AND(@cell >= DATE (2024, 10, 1), @cell <= DATE(2025, 9, 30)), {Status}, "completed")

This is what I'm currently using, but now I'm trying to account for dates within the "Actual Completion Date" column too.

The overall goal is if the row is marked "Completed" and either the "Planned Completion Date" or the "Actual Completion Date" is within the range of 10,1,24 - 9,30,25 the formula will account for that.

Find more posts tagged with

Formulas

Accepted answers

All comments

SSFeatures

Hi @Keldon, I don't think you'll be able to do this by just expanding the COUNTIFS formula.

The problem is that you essentially need to do an OR statement across the two different ranges. In other words:

{Planned Completion Date} meets the criteria OR {Actual Completion Date} meets the criteria

But this isn't possible with COUNTIFS since each range and criterion is evaluated using AND.

It looks like your formula is trying to account for the fiscal year. So what you could do is add a helper column in your source sheet named Fiscal Year and the value would be "2025".

Then you could modify your COUNTIFS formula to check if the Fiscal Year column contains the value 2025.

I hope this helps!

Keldon

Is there another formula besides using COUNTIFS that this would work for?

Kerry St. Thomas

The concept of "OR" really doesn't play nice with COUNTIFS. You might be better served with a helper column. I'm thinking type Checkbox. Do your evaluation at the row level:

=IF(AND(Status@row="Completed",OR(
AND([Planned Completion Date]@row >= DATE (2024, 10, 1), [Planned Completion Date]@row<= DATE(2025, 9, 30)),AND([Actual Completion Date]@row >= DATE (2024, 10, 1), [Actual Completion Date]@row<= DATE(2025, 9, 30))),1,0)

Then count the number of checked boxes.

(NOTE: I didn't test the formula, so it's entirely likely there's an extra or misplaced parenthesis in it.)

Good luck!

SSFeatures

@Keldon as far as I know, all of formulas that allow multiple reference ranges — evaluate them based on AND rather than OR.

Let's tag @Andrée Starå to see if he has a better idea to make this work!

TVang

@Keldon , here's another approach.

Count the completed rows where the {Planned Completion Date} is within those dates.

COUNTIFS({Status}, "Completed", {Planned Completion Date}, AND(@cell >= DATE(2024, 10, 1), @cell <= DATE(2025, 9, 30)))

Count the completed rows where the {Actual Completion Date} is within those dates AND {Planned Completion Date} IS NOT between those dates.

COUNTIFS({Status}, "Completed", {Actual Completion Date}, AND(@cell >= DATE(2024, 10, 1), @cell <= DATE(2025, 9, 30)), {Planned Completion Date}, NOT(AND(@cell >= DATE(2024, 10, 1), @cell <= DATE(2025, 9, 30))))

Add the two. =COUNTIFS() + COUNTIFS()

In this approach (for the example below) the first COUNTFS() will return 4. The second COUNTIFS() will return 1. Add the two for a count of 5.

SSFeatures

Very nice approach @TVang!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.