Filter On a Sum

AnnThompson

Hello,

I would like to filter a report, or highlight only those employees that have a sum of "Amount requesting" that is >$1000, is this possible?

KPH

You could add a SUMIF formula to that sheet to sum the amount requesting for the employee using a formula like this:

=SUMIF([Employee Name]:[Employee Name], [Employee Name]@row, [Amount Requesting]:[Amount Requesting])

This would give the total amount requested for the employee whose name is on the row. It is not a running total (though that is possible if you wanted to go further).

You could then use this within an IF function to check a box if the amount is more than 1000.

=IF(SUMIF([Employee Name]:[Employee Name], [Employee Name]@row, [Amount Requesting]:[Amount Requesting]) > 1000, 1)

The table would look like this:

You could then use the check box to filter or add conditional formatting.

You can of course take this further. For example, you could add the checkmark only at the point the request exceeds 1000. The formula would be:

=IF(SUMIF([Employee Name]$1:[Employee Name]@row, [Employee Name]@row, [Amount Requesting]$1:[Amount Requesting]@row) > 1000, 1)

And the result would be:

KPH

Unparseable is not good - that is a problem with the formula.

No match is a problem with the data.

I can just see, very faintly, in the second image that the formula entered is not correct. You are missing a part (the bit in bold)

=SUMIF([Employee Name]:[Employee Name], [Employee Name]@row, [Amount Requesting]:[Amount Requesting])

The formula you used before was fine, but it was unable to find any data that matched the logic.

KPH

Hi @AnnThompson

You could do this if you added a column to the sheet(s) and put the sum formula in that column. You could then include an IF element to check a box if this is greater than 1000 and use that checkbox to either turn on some conditional formatting, or as a filter in your report.

Would that work for you?

AnnThompson

I am not sure if that will work, as employees submit multiple (could be 5-12) submissions of receipts throughout the year for their 'healthy living' rewards, we only pay out to $1000, below are some of the column headers that are gathered via a form from employees. Once an employee hits $1000, we need an alert so we don't overpay....

KPH

You could add a SUMIF formula to that sheet to sum the amount requesting for the employee using a formula like this:

=SUMIF([Employee Name]:[Employee Name], [Employee Name]@row, [Amount Requesting]:[Amount Requesting])

This would give the total amount requested for the employee whose name is on the row. It is not a running total (though that is possible if you wanted to go further).

You could then use this within an IF function to check a box if the amount is more than 1000.

=IF(SUMIF([Employee Name]:[Employee Name], [Employee Name]@row, [Amount Requesting]:[Amount Requesting]) > 1000, 1)

The table would look like this:

You could then use the check box to filter or add conditional formatting.

You can of course take this further. For example, you could add the checkmark only at the point the request exceeds 1000. The formula would be:

=IF(SUMIF([Employee Name]$1:[Employee Name]@row, [Employee Name]@row, [Amount Requesting]$1:[Amount Requesting]@row) > 1000, 1)

And the result would be:

AnnThompson

I like that!!

I tried it, I would like to sum on Metso ID rather than name, the 1st column though, it is giving an error:

KPH

Are you able to share a screen shot that includes the Metso ID and Amount Requesting columns? The error indicates that the formula can be parsed but a match cannot be found.

AnnThompson

I got it to "unparseable'

Metso ID, is the 1st primary column, Summ of submissions is the last

KPH

Unparseable is not good - that is a problem with the formula.

No match is a problem with the data.

I can just see, very faintly, in the second image that the formula entered is not correct. You are missing a part (the bit in bold)

=SUMIF([Employee Name]:[Employee Name], [Employee Name]@row, [Amount Requesting]:[Amount Requesting])

The formula you used before was fine, but it was unable to find any data that matched the logic.

AnnThompson

Still getting an error, Since "Metso ID" is referencing another sheet, is it unable to be used in a formula?

KPH

You can use data from other sheets within your formula by creating cross sheet references. I'm not sure how your sheets are set up and what data is in what sheet, so can't give you an exact answer. Hopefully, this will help:

https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

AnnThompson

All the cells I am referencing and trying to add up, are on the same sheet.

KPH

OK, so the cells are on all one sheet but Meso ID contains a formula that references another sheet. Is that correct? If so, please check the Meso ID column and make sure there aren't any errors in that column. If there is a NO MATCH anywhere in the Meso ID column then any formula referencing that column will also return NO MATCH. You can prevent this by putting an IFERROR function around whatever formula you have in Meso ID and returning "" instead of the error message.

=IFERROR(current formula,"")

AnnThompson

Apparently, I had a space after the 'SUMIF', works like a charm now!! Love it! I also have it sending an email once the box get's checked to alert the Benefits Manager, only problem is it sends an email for each the employee has a submission, otherwise it is great!! Thanks for all your help!

KPH

That's great Ann, Well done!

If the Benefits Manager doesn't want an immediate email for each employee you could change the automation to copy the row to another sheet and then run the automation weekly on that other sheet, to send all the new rows.