Using COUNTIF, referencing data in a different sheet

tbelly50

I’m trying to count the # of rows (in a different spreadsheet), where the “initiation date” falls within the first quarter (i.e. 01/01/23 – 03/31/23).

Here is the formula I’d tried, which returns “#Invalid Ref”. (initiation date 1 and initiation date 2 are the same column)

=COUNTIF({initiation date 1} >= DATE(2023, 1, 1), AND({initiation date 2} <= DATE(2023, 3, 31)))

 Any suggestions appreciated!

 

Thanks,

Frank B.

The reason you are getting the "#Invalid Ref" error in your formula is that you are using the wrong syntax for the COUNTIF function. The correct syntax for the COUNTIF function in Smartsheet is:

=COUNTIF(range, criterion)

Where "range" is the range of cells that you want to count, and "criterion" is the condition that must be met for a cell to be counted.

To count the number of rows where the "initiation date" falls within the first quarter of 2023, you can use the following formula:

=COUNTIF({initiation date 1}:{initiation date 2}, ">="&DATE(2023,1,1), {initiation date 1}:{initiation date 2}, "<="&DATE(2023,3,31))

Here, we are using the range operator ":" to specify the range of cells that we want to count (i.e., all cells between "initiation date 1" and "initiation date 2" in each row). Then, we are using two separate criteria (one for the lower bound and one for the upper bound) joined with the "&" symbol.

Note that in Smartsheet, you can use a comma to separate multiple criteria within a single COUNTIF function, but when you want to use multiple criteria on the same range, you need to repeat the range argument for each criterion.

Frank B.

The reason you are getting the "#Invalid Ref" error in your formula is that you are using the wrong syntax for the COUNTIF function. The correct syntax for the COUNTIF function in Smartsheet is:

=COUNTIF(range, criterion)

Where "range" is the range of cells that you want to count, and "criterion" is the condition that must be met for a cell to be counted.

To count the number of rows where the "initiation date" falls within the first quarter of 2023, you can use the following formula:

=COUNTIF({initiation date 1}:{initiation date 2}, ">="&DATE(2023,1,1), {initiation date 1}:{initiation date 2}, "<="&DATE(2023,3,31))

Here, we are using the range operator ":" to specify the range of cells that we want to count (i.e., all cells between "initiation date 1" and "initiation date 2" in each row). Then, we are using two separate criteria (one for the lower bound and one for the upper bound) joined with the "&" symbol.

Note that in Smartsheet, you can use a comma to separate multiple criteria within a single COUNTIF function, but when you want to use multiple criteria on the same range, you need to repeat the range argument for each criterion.

tbelly50

Thank you!