Formula question/multiple columns from another sheet

Allyson Sullivan

Hi, I am working on a metrics sheet totaling up numbers by month from the source sheet (each row is a contract with a date, status, reviewer, etc.).

I have a formula for the total # of contracts each month but also want to produce formulas that add other columns such as: # of contracts in [Month] and Status is Pending, # contracts in [Month] and Status as Completed, # of contracts in [Month] and Reviewer is Bob, etc. (And eventually get to # of contracts in [Month], Status is Pending, and Reviewer is Bob all in one formula.)

Here's my starting formula by month (using January as the example):

=COUNTIF({Contract Support Tracker Range 1}, IFERROR(MONTH(@cell), 0) = 1)

Any suggestions to also add the subsequent condition(s) in as needed?

Thanks!

Mike TV

@Allyson Sullivan

Switch to using COUNTIFS

https://help.smartsheet.com/function/countifs

Mike TV

@Allyson Sullivan

Also, you can string multiple COUNTIF or COUNTIFS together. So if you need to count multiple columns with multiple criteria just add a + at the end of your first COUNTIF or COUNTIFS and add another one. It will add them together to give a final result.

Allyson Sullivan

Hi @Mike TV, thanks so much! I'm trying a few different things but still getting Unparseable. Does each column/condition need to be grouped both individually and together by parenthesis? Do you have an example of how one would look?

Thanks!

Allyson Sullivan

@Mike TV, should have also mentioned that both the Status and Reviewer columns are dropdowns. Thanks!

Julio S.

Hi @Allyson Sullivan,

Here are some examples of the formulas that you'd like to build.

• # of contracts in [Month] and Status is Pending: =COUNTIFS({Contract Support Tracker Range 1}, IFERROR(MONTH(@cell), 0) = 1, {Status}, "Pending")
• # contracts in [Month] and Status is Completed: =COUNTIFS({Contract Support Tracker Range 1}, IFERROR(MONTH(@cell), 0) = 1, {Status}, "Completed")
• # of contracts in [Month] and Reviewer is Bob: =COUNTIFS({Contract Support Tracker Range 1}, IFERROR(MONTH(@cell), 0) = 1, {Reviewer}, "Bob")
• =COUNTIFS({Contract Support Tracker Range 1}, IFERROR(MONTH(@cell), 0) = 1, {Status}, "Pending", {Reviewer}, "Bob")
• # of contracts in [Month], Status is Pending, and Reviewer is Bob: =COUNTIFS({Contract Support Tracker Range 1}, IFERROR(MONTH(@cell), 0) = 1, {Status}, "Pending", {Reviewer}, "Bob")

As you can observe, these are all variations of the COUNTIFS function and you can continue adding variables indefinitely with as long as the following structure is used: range (column or cells), criterion (value). Note that the above formulas take into account that all contracts belong to the same year, otherwise, the counts by month would account for that month in any year. If you wish to restrict more, you could also add the YEAR function

I hope that this can be of help

Cheers!

Julio

Allyson Sullivan

Hi @Julio S. , thanks so much for sending these! I eventually got to the same place with my formulas, but the final issue I was running into is that these other columns I'm trying to cross (Status, Reviewer, etc.) are dropdowns (that also allowed for multiple selections). When I run this formula with a data source as one of these columns, it will work- but it will only count the first selection. Since Smartsheet lists multiple dropdown selections in a single cell in alpha order, the selection that appears first in alpha order is the only one counted.

For now, we decided to only have one selection per cell anyway but it would be great to know how to fix this in the future. I tried to include HAS or CONTAINS but keep getting errors with those variations.

Thanks again!

Julio S.

Hi @Allison, thanks for clarifying. You may try with the following variations considering that your "Status" and "Reviewer" Columns are multidropdowns:

• # of contracts in [Month] and Status is Pending: =COUNTIFS({Contract Support Tracker Range 1}, IFERROR(MONTH(@cell), 0) = 1, {Status}, CONTAINS("Pending", @cell))
• # contracts in [Month] and Status is Completed: =COUNTIFS({Contract Support Tracker Range 1}, IFERROR(MONTH(@cell), 0) = 1, {Status}, CONTAINS("Completed", @cell))
• # of contracts in [Month] and Reviewer is Bob: =COUNTIFS({Contract Support Tracker Range 1}, IFERROR(MONTH(@cell), 0) = 1, {Reviewer}, CONTAINS("Bob", @cell))
• # of contracts in [Month], Status is Pending, and Reviewer is Bob: =COUNTIFS({Contract Support Tracker Range 1}, IFERROR(MONTH(@cell), 0) = 1, {Status}, CONTAINS("Pending", @cell), {Reviewer}, CONTAINS("Bob", @cell))

I hope that these can be of help.

Cheers!

Julio