Need some help from my Smartsheet Formula Experts!

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.

Find more posts tagged with

Accepted answers

Eric M Oliveira

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

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.

All comments

Paul Newcome

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

@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.