COUNTIF(DATE("Reference another Sheet Column") Date=Year To Date ) ***HELP***

J Smith

I have tired a variety of different ways to get this formula to work. Basically, I need to count the number of cells in a column from another sheet that are within this calendar year. I am surprised there is not a YTD command. Is there a work around.

Toufong Vang

Hi, @J Smith,

The formula below returns the count of rows from the other sheet that are between Jan. 1st of the current year and today.

= COUNTIF({Column_from_other_sheet_containing_date_value}, AND(@cell>=DATE(YEAR(TODAY()),1,1), @cell<=TODAY()))

The formula below returns the count of rows from the other sheet that are in the current year.

= COUNTIF({Column_from_other_sheet_containing_date_value}, YEAR(@cell) = YEAR(TODAY()))

The syntax for the COUNTIF() function is COUNTIF( range, criterion).

Cheers!

Toufong Vang

Hi, @J Smith,

The formula below returns the count of rows from the other sheet that are between Jan. 1st of the current year and today.

= COUNTIF({Column_from_other_sheet_containing_date_value}, AND(@cell>=DATE(YEAR(TODAY()),1,1), @cell<=TODAY()))

The formula below returns the count of rows from the other sheet that are in the current year.

= COUNTIF({Column_from_other_sheet_containing_date_value}, YEAR(@cell) = YEAR(TODAY()))

The syntax for the COUNTIF() function is COUNTIF( range, criterion).

Cheers!

J Smith

The 1st option worked! Thank you so much.

The 2nd option returns "#INVALID DATA TYPE" It seems like a much simpler way to get to the goal but I couldn't make it work. But I am grateful the first one worked!

Thank you

Toufong Vang

Try the following...

=COUNTIF(Start:Start, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

The original assumed that there wouldn't be empty cells in the column. The revised formula takes into account that some cells might be blank.

J Smith

Still on the same idea but with an additional column to consider, do you happen to have a solution for this one?

=SUMIF({Source_Sheet_1stColumn}, ({Same_Source_Sheet_2ndColumn} (@cell >= DATE(YEAR(TODAY()), 1, 1), @cell <= TODAY())))

Jake Gustafson

This thread helped me reconstruct some formulas. I've run into some data limitations recently with too many cell references and so I'm trying to fix some technical debt to make workflows and sheets operational again. Let me ask if doing things like this, with the reference to a single date field is more efficient than to some helper columns.

Here is what I had:

=COUNTIFS({NCP QA Comp Yr Arc2022-Q3}, Year@row, {NCP QA Comp Mo Arc2022-Q3}, Index@row, {NCP Resp Whse Arc2022-Q3}, [Glenwood B1]#)

• Where {NCP QA Comp Yr Arc2022-Q3} is a helper column that is taking the YEAR of the QA Complete Date field.
• Where {NCP QA Comp Mo Arc2022-Q3} is a helper column that is taking the MONTH of the QA Complete Date field.

Now what I've replaced that formula with is this:

=COUNTIFS({NCP QA Comp Date Arc2022-Q3}, AND(IFERROR(YEAR(@cell), 0) = Year@row, IFERROR(MONTH(@cell), 0) = Index@row), {NCP Resp Whse Arc2022-Q3}, [Glenwood B1]#)

• Where I'm only referencing the QA Complete Date field (with {NCP QA Comp Date Arc2022-Q3}) but making sure it meets my Year and Month criteria.

As I type this out, it would make sense that the new/second option is better because the first formula references 3 separate fields (Year, Month, Warehouse), so if there are 100 records, that's 300 cell references. But the second formulas only reference 2 separate fields (Date, Warehouse), so those 100 records would only have 200 cell references.

#continuousimprovement