Using HAS function with SUMIF - multiple references

Question

Hi all, reaching out to see if of you have ideas; I'm hoping this makes sense:

I'm having some trouble getting my formulas to pull data in correctly. I currently have a sheet (primary data) where I have data in rows where each row is a single project, with columns identifying persons, $ values, unique project identifier, and other non financial data elements. I have a separate sheet (sheet I am having issues with - totals sheet) that pulls and adds the rows base on the content of four of the columns (Range 1 = FY Quarter, Range 3 = Total $ amount, Range 7 = Person, Range 8 = multiple persons listed using a drop down list).

While I have no issues with a cell adding up $ values (range 3) based on the range 1- quarter and range 7 - person, I am having issues in the range 8 column adding in totals in instances where a specific person is included in range 8 where there are multiple people. I need to have separate columns as a person (Jane Doe) could be the lead project manager (identified in range 7) or be part of the project (range 8) and need to add up the dollar values for both instances in the event Jane Doe is included in either range 7 or 8. Below is my current formula that is excluding instances when Jane Doe is one of several listed in range 8. This formula is meant to be a total of the whole year, so no need to break out by quarter, but at present, range 8 does not seem to function and does not pull in values from range 3 to add if the person is listed among others. What am I missing here:

=SUMIF({FY26 External Sponsored Programs Activity Range 7}, "JaneDoe", {FY26 External Sponsored Programs Activity Range 3}) + SUMIF({FY26 External Sponsored Programs Activity Range 8}, "JaneDoe", {FY26 External Sponsored Programs Activity Range 3})

In doing basic counts for the total number of projects (so not a dollar value), I did have success using the HAS functions (formula below), but can't seem to replicate this for a column with a dollar value.

=COUNTIFS({FY26 External Sponsored Programs Activity Range 1}, "Q1", {FY26 External Sponsored Programs Activity Range 7}, "Person2") + COUNTIFS({FY26 External Sponsored Programs Activity Range 1}, "Q1", {FY26 External Sponsored Programs Activity Range 8}, HAS(@cell , "Person2"))

Paul Newcome · Answer

I would suggest a multi-select dropdown helper column (that can be hidden after setting up) that combines both Investigator columns using this formula:

=[First Investigator Column Name]@row + IF([Second Investigator Column]@row <> "", CHAR(10) + [Second Investigator Column]@row, "")

Then you can use a single SUMIFS and run your HAS on this:

=SUMIFS({Source Sheet $$ Column}, {Source Sheet Helper Column}, HAS(@cell, [Investigator Column Name]@row)

RRust · Answer

Hi Paul,

Hopefully this makes better sense. In this instance, an individual (Danielson) is listed in Range 7 and 8. However, in my other spreadsheet, using the first formula above, the $ value from the line where Danielson is listed in the Range 8 column is missing. I am wanting to combine the $ of their projects in both instances, when they are included in range 7 and in range 8. The second screenshot is the column and formula for the numbers to pull in - I am omitting other entries which is why the cumulative totals listed in the first screenshot do not match the second.

Paul Newcome · Answer

Are you able to provide some screenshots (with mock data if necessary) for context?