Need some help from my Smartsheet Formula Experts!

Pam Ferguson · 04/24/20

I need to count how many times a reject appears on my listing that reject and appear on my reject listing again. This would actually result in a duplicate second row on my sheet with a new create date. All other data would be exactly the same. It most likely would have a different week number. How would I best be able to figure out the # of existing rejections that reject again in that current week?

I have the following columns: Current Week, Week Number, Invoice #

I will need to report this data every week and have created a metrics sheet to capture this weekly data. Just not sure how to configure the formula to count a row that may be on our sheet 3 times, and have rejected 2 times in the same week.

We currently do not have a Parent/Child structure.

Eric M Oliveira · 05/01/20

Hello,

With Paul's formula, you could adjust it to automatically look at the new entry of every row and count accordingly. Based on your desired description it does seem that a COUNTIFS formula would be the best formula to achieve your desired goal. You could adjust it to perform the different desired specific calculations.

For Example:

You can create another column, for the sake of this example let's call the column "Rejected Amount", that contains a formula in each cell that counts the amount of times a particular ID number appears in a week.

The formula could reference the ID at the row level and be automatically calculated. This formula could appear similar to this.

=COUNTIFS([Invoice #]:[Invoice #], [ID Column]@row, [Week Number]:[Week Number], WEEKNUMBER(TODAY()))

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

@row: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

The @row function gives the formula the ability to have the newly entered ID number be automatically the value we're searching for. This would achieve the could of how many times a specific item appears this week.

You can then create a Report to view all values that have a # greater than 1. This Help Center article outlines in further detail Reports: https://help.smartsheet.com/articles/522214-creating-reports

You could also create a metric sheet, for the purpose of being referenced by a Dashboard, that lists all potential ID numbers and performs a similar count to see how many times they appear individually. Which when finished could look similar:

=COUNTIFS({Invoice Range}, [ID Column]@row, {Invoice Range}, WEEKNUMBER(TODAY()))

In comparison to how many in total were rejected this week.

=COUNTIFS({Rejected Amount Range}, >1, (Invoice Range}, WEEKNUMBER(TODAY()))

Have a wonderful day.

Cheers,

Eric

Smartsheet Technical Support

Paul Newcome · 05/01/20

My apologies. I read "how many times a reject appears" as searching for something specific.

To be able to search the entire sheet for variable Invoice #'s, Eric's suggestion of an additional column should do the trick, but I would use a slightly different formula of:

=COUNTIFS([Invoice #]:[Invoice #], [Invoice #]@row, [Week Number]:[Week Number], WEEKNUMBER(TODAY()))

The difference is searching the entire [Invoice #] column for the data that is in the [Invoice #] column and not the [ID Column].

To only show one instance of each repeat, you could add in a checkbox column in addition to the above and use something along the lines of

=IF(COUNTIFS([Invoice #]$1:[Invoice #]@row, [Invoice #]@row, [Count Column]$1:[Count Column]@row, @cell > 1) > 1, 1)

This will check the box on each row for the first occurrence of that particular duplicate.

Then you could pull a report based on this column being checked.

Paul Newcome · 04/30/20

You could try something along the lines of...

=COUNTIFS([Invoice #]:[Invoice #], "12345", [Week Number]:[Week Number], WEEKNUMBER(TODAY()))

Just change the Invoice Number to whatever invoice you are wanting to count for.

Pam Ferguson · 04/30/20

@Paul Newcome That's great but my concern is I don't know which invoice #'s actually reject, so I wouldn't want to manually enter each of the numbers since it would be random.

Would Pivot be a better option? Basically I want to see how many times a particular invoice may be rejected. If the invoice was rejected on a Monday, and it was resubmitted, then rejected again later in the week, I would like to be able to capture that the same biller had the invoice rejected twice. That way we could identify potential issues.

Eric M Oliveira · 05/01/20