SUM multiple values in 1 column when 2 other columns have specific values based on unique ID
Hello!
I am not a seasoned user and have been unable to find a solve for my function issue.
I have sheet 1 (Request pdf) that is populated by a form. Employees request funds and can have more than 1 entry.
On sheet 2 (allocation pdf), each employee has a set number of funds available.
I want to add all of the $ requested by a specific employee BUT only if 2 other columns have the right values to reflect total $ used
I am using the {BusinessEmail} as the unique identifier on both sheets. I am using {$Column} for the Reimbursement requested on sheet 1. The other 2 conditions on sheet 1 are {RequestColumn} has to be "Reimbursement" and the {StatusColumn} has to be "Processed" to sum all the values to return to the column on Sheet 2 for funds available.
This formula doesn't give me errors but isn't adding the amounts:
=SUM(COLLECT({$Column}, {EmailColumn}, @cell = [Business Email]@row, {StatusColumn}, “Processed”,{RequestColumn}, "Reimbursement Submission (includes VTNE cost)"))
Does my email column need to be text instead of a contact?
Answers
-
Hey @decrom
If both email columns are contacts, you should be ok. There will not be a match if you have the contact name in one sheet and an email address in the other. (The data is completely redacted so I could not see your data format).
As a test, try adding the @cell= to your other criteria. If you are still obtaining a zero, as a test, try SUM({$Column}). If that doesn't produce a value, delete {$Column} and go through the process of reinserting. One by one, remove the criteria from your Collect so you can find your culprit.
=SUM(COLLECT({$Column}, {EmailColumn}, @cell = [Business Email]@row, {StatusColumn}, @cell=“Processed”,{RequestColumn}, @cell="Reimbursement Submission (includes VTNE cost)"))
-
Thank you! I found the broken piece, and also realized the emails were listed out on one and had names in the second sheet.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 303 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!