SUM multiple values in 1 column when 2 other columns have specific values based on unique ID

decrom

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?

CE Funds Requests and Reimbursements.pdf

CE Funds Master Allocations.pdf

Kelly Moore

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)"))

decrom

Thank you! I found the broken piece, and also realized the emails were listed out on one and had names in the second sheet.