Amount of Requests per Lawyer

fennerb

Hello,

I'm trying to find out how many requests a lawyer gets per month based on a few things:

-AFID (Auto Fill-In Date)

-Type of Request = FOIA

-Lawyer's Staff name

-Status (Red, Yellow, Green, Blue; However, these are not in as words, they're in as "Visual Symbols")

This is what I have right now (changing name for anonymity purposes): =COUNTIFS({AFID}, AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell), 0) = 2019, {Type of Request}, FIND("FOIA", @cell), AND(IF({Law Staff}, FIND("Karen Cappa", @cell), IF({Status} = GREEN) > 0)

Paul Newcome

Try this one...

=COUNTIFS({AFID}, AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell), 0) = 2019), {Type of Request}, FIND("FOIA", @cell) > 0, {Law Staff}, FIND("Karen Cappa", @cell) > 0, {Status}, "Green")

fennerb

It came back as "INCORRECT ARGUMENT SET." :/

Paul Newcome

Can you copy the formula that is generating the error from the sheet itself and paste it here so I can see EXACTLY what you are using?

fennerb

=COUNTIFS({AFID}, AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell), 0) = 2019), {Type of Request}, FIND("FOIA", @cell) > 0, {Law Staff}, FIND("Ivars Steins", @cell) > 0, {Status}, "Green")

Paul Newcome

The formula itself should not be producing an error.

Check your ranges to see if there are any errors in them. That could also generate an error.

fennerb

I refreshed the page, and it no longer produced an error. It did, however, provide 0 as a value.

Paul Newcome

Keep in mind...

The FIND function is case sensitive. For example: If you have FOIA in your formula, but you have Foia in your sheet then it will not be counted.

Paul Newcome

Also check your column types as well. Primarily the AFID column.

Make sure that it is a date type column. If it is an auto-generated type of column that provides a date/time stamp, then you will need to incorporate the DATEONLY function like so...

=COUNTIFS({AFID}, AND(IFERROR(MONTH(DATEONLY(@cell)), 0) = 12, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2019), {Type of Request}, FIND("FOIA", @cell) > 0, {Law Staff}, FIND("Ivars Steins", @cell) > 0, {Status}, "Green")

fennerb

The AFID column is auto-generated and provides a date/time stamp. So, that makes so much more sense to me now! "Type of Request" and "Law Staff" are single-select drop-down columns to make sure we have continuity and prevent any misspellings. The "Status" column is generated by a formula using the AFID date and when it was sent to the Law Department.

I just tried the new formula, and it still yielded 0 after I refreshed it a few times. I'm copying and pasting what I used for reference: =COUNTIFS({AFID}, AND(IFERROR(MONTH(DATEONLY(@cell)), 0) = 12, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2019), {Type of Request}, FIND("FOIA", @cell) > 0, {Law Staff}, FIND("Ivars Steins", @cell) > 0, {Status}, "Green")

And, if we need it, here's the "Status" formula that we've been using to determine the colors (this was prior to me renaming the column "AFID"): =IF(ISBLANK([Information Requested -Auto Filled in Date]1), "", IF(ISDATE([Date Entire Request is Complete/Sent to Law]1), "Blue", IF(TODAY() >= [Information Requested -Auto Filled in Date]1 + 14, "Red", IF(TODAY() >= [Information Requested -Auto Filled in Date]1 + 7, "Yellow", IF(TODAY() >= [Information Requested -Auto Filled in Date]1, "Green")))))

Paul Newcome

Hmm... That is odd. Here are a couple more things to look into...

1. This happens to me on occasion. When I am creating a cross sheet reference, I select the column header too quickly before the sheet has time to fully load. Double check that your ranges are in fact correct. If you selected the column before the sheet was completely loaded, the range could have reverted back to the cell in the top left corner of the sheet.
2. Go to the source sheet and create a filter that matches exactly what you are trying to build a formula for. Enter the same ranges/criteria in the same order. See what happens when you apply that filter.

Let me know what the results are. We are fast approaching reaching out to support.

I had an issue a few weeks ago where I was getting obviously incorrect counts from a COUNTIFS. Support was able to refresh the sheet itself on the back-end which got it working correctly. I like to try to exhaust all other possibilities though before reaching out to them.

Eric M Oliveira

Hello,

 

Happy to help, it seems in all of the above examples the syntax is slightly off with the COUNTIFS function. If you're trying to find out how many requests a lawyer gets per month based on a few things:

 

-AFID (Auto Fill-In Date)

-Type of Request = FOIA

-Lawyer's Staff name

-Status (Red, Yellow, Green, Blue; However, these are not in as words, they're in as "Visual Symbols")

 

you can achieve this with a COUNTIF function similar to the below. 

 

=COUNTIFS({AFID}, MONTH(@cell) = 12, {AFID}, YEAR(@cell) = 2019, {Type of Request}, "FOIA", {Law Staff}, "Karen Cappa", {Status}, "Green")

 

Note: You'll want to make sure all of the cross-sheet references are referencing the correct columns. Also, that the text values are spelled exactly as they are on the reference sheet, Smartsheet is case sensitive when it comes to formulas. 

 

If you're utilizing multi-select columns you'll want to utilize a formula similar to the below:

 

=COUNTIFS({AFID}, MONTH(@cell) = 12, {AFID}, YEAR(@cell) = 2019, {Type of Request}, HAS(@cell, "FOIA"), {Law Staff}, HAS(@cell, "Karen Cappa"), {Status}, "Green")

 

The topics we discussed are further outlined by the Help Center articles below. 

• COUNTIFS: https://help.smartsheet.com/function/countifs
• HAS: https://help.smartsheet.com/function/has

 

If the issue continues after performing the above please click HERE and we can set aside some time for a screen share to correct this issue. 

 

Have a wonderful day. Thank you for contacting Smartsheet Support.

 

Cheers,

Eric

Smartsheet Technical Support

Paul Newcome

@Eric M Oliveira

I must be missing it... Here is a copy/paste of one of the above COUNTIFS:

=COUNTIFS({AFID}, AND(IFERROR(MONTH(DATEONLY(@cell)), 0) = 12, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2019), {Type of Request}, FIND("FOIA", @cell) > 0, {Law Staff}, FIND("Ivars Steins", @cell) > 0, {Status}, "Green")

I can't seem to find where the syntax is wrong.

Additionally... Wouldn't the FIND function work within a multi-select type of column if we are trying to base the count on any rows that include that data even if there is other data in the cell (such as multiple names)?

Eric M Oliveira

@Paul Newcome

Hi Paul,

Glancing at the formula quick the section "AND(IFERROR(MONTH(DATEONLY(@cell)), 0) = 12, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2019)" may be causing the error if you're receiving one, the syntax for DATEONLY(@cell) may be causing the issue because it doesn't seem to be comparing itself to anything. When utilizing the MONTH function in the example you wouldn't need the DATEONLY, written as MONTH(@cell) = 12, same with the YEAR function.

In regards to the FIND versus the HAS, you could possibly utilize the FIND, however, for this example, the HAS would be more accurate. FIND locate items in a multi-select that are similar to the given text HAS searching for an exact match of a value, including multi-contact or multi-select dropdown column cells or ranges. Returns true if found, false if not found. These topics are further outlined by the Help Center articles below. 

HAS: https://help.smartsheet.com/function/has

FIND: https://help.smartsheet.com/function/find

Have a wonderful day. Thank you for contacting Smartsheet Support.

Cheers,

Eric

Smartsheet Technical Support

Paul Newcome

@Eric M Oliveira

This section is looking at the {AFID} range which is a column that is an autogenerated Date/Time stamp. DATEONLY is the correct function if we are trying to pull the date from the cell which we are for the MONTH/YEAR functions.

I actually use this very regularly and it was even suggested as a solution from someone else in Support that I use this even on regular date columns that have text and/or blank cells within the range (re: Ticket Number 03323469).

I use this very frequently without issue.

As for the FIND vs HAS...

When I included the FIND function in my solution, I did not know if it was single select or multi select. Where I disagree with your statement though is

"FIND locate items in a multi-select that are similar to the given text HAS searching for an exact match of a value"

The FIND function will not locate items that are similar. It will find that exact match within a string to include being case sensitive.

The HAS function will only locate cells that are an exact match, so if you are looking for "John Doe" in a multi select column, it will only locate those cells that are strictly "John Doe". If "John Doe" and Jane Doe" are both in a cell, the HAS function will not locate it.

When the range is a single select, FIND and HAS will function the exact same way when used properly.

Even after discovering the column type was a single select, I stuck with the FIND function because that is what was used in the original formula and only needed a minor tweak to get it working. Since it works the same way, I didn't see any reason to change it.