Countifs Criterion referencing other columns

Eric Nelson

I am trying to count data based on a completion date being after the request date (data error checking).

"=COUNTIFS(Completed:Completed, >Requested@cell)"

This give me and #Unparseable error

Genevieve P.

Hi @Eric Nelson

I see you used @cell as the row reference in this formula. Try using @row instead! This tells the formula to look at the cell in the "Requested" column for this current row, or @ this row.

=COUNTIFS(Completed:Completed, > Requested@row)

See: Create Efficient Formulas with @cell and @row

Cheers,

Genevieve

Eric Nelson

=COUNTIFS(Completed:Completed,

>Requested:Requested) doesn't work

=COUNTIFS(Completed:Completed, >Requested@row) doesn't work either

Genevieve P.

Hi @Eric Nelson

The second formula has the correct syntax:

=COUNTIFS(Completed:Completed, >Requested@row)

When you say it doesn't work, are you receiving an error or an incorrect result? This formula assumes that your "Completed" column is in the current sheet and is set to a Date format. It's then looking to count how many rows have a date that's in the future compared to the date in the one cell of this row, in the Requested column. Is that what you're looking to do?

If not, it would be helpful to see screen captures of your sheet set-up, but please block out sensitive data.

Cheers,

Genevieve

Eric Nelson

I am doing this in a sheet summary (should have said that before). It works fine in a column in the sheet.

=SUMIFS(Completed:Completed, >[VWRS Requested]@row), both Completed and VWRS Requested are date values.

Genevieve P.

Hey @Eric Nelson

Where is the "Requested" cell located? Is it in the Sheet Summary as well, or is it in the Grid of the sheet? This changes how the formula should be written.

For example, in a sheet summary you reference the field with a #, like so:

=SUMIFS(Completed:Completed, >[VWRS Requested]#)

If it's in the sheet, you'll need to add the row number itself after the column reference, like so:

=SUMIFS(Completed:Completed, >[VWRS Requested]1)

^That's for row one. If your value is in row 5, you would use:

=SUMIFS(Completed:Completed, >[VWRS Requested]5)

Does that make sense? See: Create a Cell or Column Reference in a Formula