# Need some help from my Smartsheet Formula Experts!

Options
Overachievers Alumni
edited 04/27/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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• Overachievers Alumni
Options

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

• ✭✭✭✭✭✭
Options

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.

• Overachievers Alumni
Options

Eric and Paul you are wonderful!!!! Thank you so much! I love this community!

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!