TODAY Function with COUNTIFS

I have a formula just now which is :

=COUNTIFS({Business Case}, $Measure5, {Business Unit}, [Brand X]$3, {PROJECT STATUS}, OR(@cell = "Shipped", @cell = "In Progress"))

This is count the number on 'on time' and 'late business case's where business unit is brand X where the project status is either 'shipped' or 'in progress'.

I also wanted to add the formula to include a parameter of looking at a date column, so it includes in the count from TODAY up to 12 months from that date [-365].

I found for the TODAY function the formula is :

=COUNTIF([Date Column]:[Date Column], @cell> TODAY(-365))

I'm trying to incorporate the TODAY function into my existing formula which works, but failing - any help?

=COUNTIFS({Business Case}, $Measure5, {Business Unit}, [Brand X]$3, {PROJECT STATUS}, OR(@cell = "Shipped", @cell = "In Progress"), [BC Date]:[BC Date], @cell> TODAY(-365))

Many thanks.

Lindsay

Find more posts tagged with

Accepted answers

L_123

{Business Case} {Business Unit} {Project Status}

are all references to another sheet.

[BC Date]:[BC Date]

is a reference to the current sheet.

If the current sheet doesn't have the exact same amount of rows as the referenced sheet you won't be able to do a countif incorporating both sheets using column ranges. I'm not sure what your objective is, as I haven't seen your sheet layout, but here are 2 potential solutions depending on sheet layout (posting a picture of your sheets would really help if neither of these work for you)

=if([BC Date]@row >today(-365),COUNTIFS({Business Case}, $Measure5, {Business Unit}, [Brand X]$3, {PROJECT STATUS}, OR(@cell = "Shipped", @cell = "In Progress"

=COUNTIFS({Business Case}, $Measure5, {Business Unit}, [Brand X]$3, {PROJECT STATUS}, OR(@cell = "Shipped", @cell = "In Progress"), {BC Date}, @cell> TODAY(-365

All comments

L_123

It is failing because the number of cells in each reference in your other sheet references are different than that of the same sheet reference BC Date.

Lindsay Wilson

@L@123 thanks for looking at my query!

Not too sure I understand what you mean. What you suggest the below?

=COUNTIFS({Business Case}, $Measure5, {Business Unit}, [Brand X]$3, {PROJECT STATUS}, OR(@cell = "Shipped", @cell = "In Progress"), [BC Date], @cell> TODAY(-365))

Lindsay

L_123

{Business Case} {Business Unit} {Project Status}

are all references to another sheet.

[BC Date]:[BC Date]

is a reference to the current sheet.

=if([BC Date]@row >today(-365),COUNTIFS({Business Case}, $Measure5, {Business Unit}, [Brand X]$3, {PROJECT STATUS}, OR(@cell = "Shipped", @cell = "In Progress"

=COUNTIFS({Business Case}, $Measure5, {Business Unit}, [Brand X]$3, {PROJECT STATUS}, OR(@cell = "Shipped", @cell = "In Progress"), {BC Date}, @cell> TODAY(-365

Lindsay Wilson

@L@123 the second worked! thank you so much spent days looking at this!

Lindsay

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.