Countifs Criterion referencing other columns
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
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
=COUNTIFS(Completed:Completed,
>Requested:Requested) doesn't work
=COUNTIFS(Completed:Completed, >Requested@row) doesn't work either
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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.
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!