Formula Guidance

I'm working on a project that involves matching organization names across two sheets, but I'm running into some complexity and could use guidance on how to approach it.

The source sheet contains all organizations that have received donations since 2020, including donation amounts, dates, and progress status.
The secondary sheet includes a smaller list of specific organizations I need to focus on.

My goal is to sum the donation amounts from the source sheet only when:

The “Organization Name” matches one from the secondary sheet,
The “Date of Submission” is in 2025, and
The “Progress” status is marked as "Complete."

My thoughts were an index and match formula. However, I’m unsure how to account for the additional criteria. Any recommendations on what formula to use?

Find more posts tagged with

Accepted answers

Paul Newcome

You don't need the helper column for the year. You can reference that directly in your SUMIFS.

=SUMIFS({Donation Amount}, {Organization Name}, @cell = [Organization Name]@row, {Date of Submission}, IFERROR(YEAR(@cell), 0) = 2025, {Progress}, @cell = "Complete")

Also note that "@row" and "@cell" are both case sensitive and should be all lowercase.

Paul Newcome

There is an extra closing parenthesis at the end that needs removed.

All comments

VBAGuru

You could easily use SUMIFS with the reference another sheet. Something like:

=SUMIFS({Donation Amount},{Organization Name},[Organization Name]@row,{Date of Submission},"2025",{Progress},"Complete")

Any thing with {} are were you reference the secondary sheet and would need to column level reference. The {}@row is on that specific sheet

hjgeorge

Thank you, @VBAGuru. I ran the formula, but it's returning a $0 result. Should I add a contains function? Also, the 'Date of Submission' field is formatted as a full date (e.g., 01/01/2025), and I need to filter for entries that include the year 2025. Any recommendations?

VBAGuru

@hjgeorge If the Organization Name is not an exact match, then you would need a contains function. With not having the year separated out, I would suggest a 'Helper' column for the year. This could be hidden on that data sheet and would just be a column formula, =YEAR([Date]@Row).

Paul Newcome

You don't need the helper column for the year. You can reference that directly in your SUMIFS.

=SUMIFS({Donation Amount}, {Organization Name}, @cell = [Organization Name]@row, {Date of Submission}, IFERROR(YEAR(@cell), 0) = 2025, {Progress}, @cell = "Complete")

Also note that "@row" and "@cell" are both case sensitive and should be all lowercase.

hjgeorge

@VBAGuru. I removed the date from the formula to see what results I could pull. The 'Progress' column is a drop-down menu. Could that affect the formula? I expected at least one result with data, but it's still returning $0.

Paul Newcome

@hjgeorge Are you able to provide some screenshots for context?

hjgeorge

The data sheet contains several columns, so I took a screenshot highlighting the ones I'm focused on. These include an organization that matches an organization listed on my second sheet. I don’t have admin rights on the data sheet, but I was given editor access to work with the data.

Paul Newcome

There's the issue. Your formula is looking for "Complete", but your source data has "Completed" with the "d" there on the end.

hjgeorge

I've tried the formula provided above and getting an #unparseable error. Did I misplace a piece of the formula?

=SUMIFS({Charitable Request Intake Form_Donation Amount}, {Charitable Request Intake Form_Org Name}, @cell = [COI Partnership]@row, {Charitable Request Intake Form_Date}, IFERROR(Year(@cell ), 0) = 2025, {Charitable Request Intake Form_Progress}, @cell = "Completed"))

Paul Newcome

There is an extra closing parenthesis at the end that needs removed.

hjgeorge

Thank you very much! The formula captures exactly what I needed.

Paul Newcome

Happy to help. 👍️

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.