TODAY Function with COUNTIFS

Lindsay Wilson

Hi

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

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"

or

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

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 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"

or

=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