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?

CE Funds Requests and Reimbursements.pdf

CE Funds Master Allocations.pdf

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.