Formula for counting publications in a calendar year

SiddV47

Hi,

Here is a background on what my ask is:

We track publications on Smartsheets and the publication activity column has the following drop-down options:

Journal Article

Meeting/ Conference Abstract/ Poster

Book chapter

Non-authorship contributions to publications (acknowledgement)

Non-scientific presentation (podium/ keynote/ panel discussion)

Media Mentions

I would like to count:

1. the total no. of "Journal Articles" that were "Accepted for Publication(Attach final pdf copy)" in the calendar year 2023 (between 01/01/2023 - 12/31/2023) and similarly
2. the total no. of "Journal Articles" that are "Under Peer Review by Journal/ Professional Society (Attach abstract)" in the calendar year 2023 (between 01/01/2023 - 12/31/2023)
3. the total no. of "Meeting/ Conference Abstract/ Poster" that were "Presented at a conference/ symposium/ workshop" in the calendar year 2023 (between 01/01/2023 - 12/31/2023)
4. the total no. of "Meeting/ Conference Abstract/ Poster that were "Published and/or presented at a conference" in the calendar year 2023 (between 01/01/2023 - 12/31/2023)

My formula for point #1 gives me an error - #Invalid Operation

=COUNTIFS({Publication Tracker Status}, "Accepted for Publication (Attach final pdf copy)", >=DATE(2023, 1, 1), {Publication date range}, <=DATE(2023, 12, 31))

Please share your expert thoughts and or new formula.

Thank you!

Nick Korna

It would be an OR, similar to how the dates are already done with an AND:

=COUNTIFS({Activity type}, "Meeting/ Conference Abstract/ Poster", {Status}, OR(@cell="Presented at a conference/ symposium/ workshop",@cell="Published and/or presented at a conference"), {Publication date range}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 12, 31)))

The other easy way if you weren't sure on how to do it would be to do have 2 COUNTIFS and add them together (where you just change the status for the 2nd one):

=COUNTIFS({Activity type}, "Meeting/ Conference Abstract/ Poster", {Status}, "Presented at a conference/ symposium/ workshop", {Publication date range}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 12, 31)))+COUNTIFS({Activity type}, "Meeting/ Conference Abstract/ Poster", {Status}, "Published and/or presented at a conference", {Publication date range}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 12, 31)))

Use whichever you prefer! 🙂

Lauren Dominique

Try this for need #1:

Note: I wrote the formula as if you were using the Sheet Summary field to capture the metrics. If you are using a different grid as a metrics sheet, then you’d change the [column name]:[column name] to {cross sheet reference}.

=COUNTIFS([Activity Type]:[Activity Type], “Journal Article”, [Status (single select)]:[Status (single select)], “Accepted for Publication (Attach final pdf copy)", AND([Publication or Presentation Date]:[Publication or Presentation Date] >=DATE(2023, 1, 1),[Publication or Presentation Date]:[Publication or Presentation Date] <=DATE(2023, 12, 31))

Also, a best practice I’ve gotten into when naming columns is to use as short/simple names as possible- it makes it much easier especially for when writing out formulas like the above. For example, I might recommend changing the date field to simply “Date” and then if you wanted that extra level of clarification, you could add “Date on which the presentation or publication took place” as the Column Description (just right click on any column and select “Edit Column Description”). Likewise, I also try to avoid having parenthesis in my column names since Smartsheet used parenthesis to read functions (so again, less confusing to exclude them when writing formulas). And, I also might recommend getting rid of the “(single select)” from the column name, not only to simplify the name by removing the parenthesis, but also because, when you have the single-select setting turned on, Smartsheet would never let someone multi-select anyways.

All said… if the above formula works, you’d just make some simple swaps for your other needs. i.e., for #3:

=COUNTIFS([Activity Type]:[Activity Type], “Meeting/Conference Abstract/Poster”, [Status (single select)]:[Status (single select)], “Presented at a conference/symposium/workshop", AND([Publication or Presentation Date]:[Publication or Presentation Date] >=DATE(2023, 1, 1),[Publication or Presentation Date]:[Publication or Presentation Date] <=DATE(2023, 12, 31))

SiddV47

Hi Lauren,

Thank you for taking time to read my post and share your insight. The formula I created using your formula as a reference is for cross sheet reference:

=COUNTIFS({Activity type}, “Journal Article”, {Status}, “Accepted for Publication (Attach final pdf copy)", AND({Publication date range} >=DATE(2023, 1, 1), {Publication date range} <=DATE(2023, 12, 31))

But I am getting an an error #unparseable. Please review and let me know where am I going wrong.

Appreciate your comments. Thanks again!

Nick Korna

Amend the last section referring to the dates to one of these:

{Publication date range},AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 12, 31))

OR

{Publication date range},>=DATE(2023, 1, 1),{Publication date range},<=DATE(2023, 12, 31)

These will obviously be within your COUNTIFS, so will need another bracket at the end (if the final criteria).

Hopefully this is the last bit of the puzzle, but if you have any other problems/questions let us know.

SiddV47

Hi Nick,

I used both your suggested iterations. The formula still give an error.

=COUNTIFS({Activity type}, “Journal Article”, {Status}, “Accepted for Publication (Attach final pdf copy)", AND ({Publication date range},AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 12, 31))

OR

=COUNTIFS({Activity type}, “Journal Article”, {Status}, “Accepted for Publication (Attach final pdf copy)", AND {Publication date range},>=DATE(2023, 1, 1),{Publication date range},<=DATE(2023, 12, 31)

Please it would be appreciated if you could type the full formula as I always tend to mess up with the parenthesis/ brackets.

Nick Korna

Hi @SiddV47

=COUNTIFS({Activity Type}, "Journal Article", {Status}, "Accepted for Publication (Attach final pdf copy)", {Publication Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 12, 31)))

Example of it in action:

Base data set:

If you're still having issues, let us know - the only other thing I can think of is double checking your cross sheet references are the relevant columns and that your "Publication Date" column is set to Date (for the date range checks).

SiddV47

Hi @Nick Korna

This iteration of the formula worked. Thank you so much!

One last query, see the image below:

As you may notice this formula, only counts the selected activity if the status selected is "Presented at a conference/ symposium/ workshop". I would like to also include the status "Published and/or presented at a conference" in this formula.

=COUNTIFS({Activity type}, "Meeting/ Conference Abstract/ Poster", {Status}, "Presented at a conference/ symposium/ workshop", {Publication date range}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 12, 31)))

Once again, appreciate your help!

Nick Korna

It would be an OR, similar to how the dates are already done with an AND:

=COUNTIFS({Activity type}, "Meeting/ Conference Abstract/ Poster", {Status}, OR(@cell="Presented at a conference/ symposium/ workshop",@cell="Published and/or presented at a conference"), {Publication date range}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 12, 31)))

The other easy way if you weren't sure on how to do it would be to do have 2 COUNTIFS and add them together (where you just change the status for the 2nd one):

=COUNTIFS({Activity type}, "Meeting/ Conference Abstract/ Poster", {Status}, "Presented at a conference/ symposium/ workshop", {Publication date range}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 12, 31)))+COUNTIFS({Activity type}, "Meeting/ Conference Abstract/ Poster", {Status}, "Published and/or presented at a conference", {Publication date range}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 12, 31)))

Use whichever you prefer! 🙂

SiddV47

@Nick Korna - Thank you again. This thread was very helpful. Appreciate your time and thoughts to resolve my questions.

Nick Korna

No problem at all, glad to have helped!