SUMIFS Formula Help

Hello there,

I'm having trouble with an excel SUMIFS Formula that I'd love some assistance with. The formula is using an opportunity ID as the reference.

Problem: When I add up the values using a SUMIFS statement, there are a number of data values that are being incorrectly included - I need to make sure that the sum product leaves these values out.

Goal: Add up the values in the cells of Data Sheet 1 that do not share an Opportunity ID with range A2:A25. AN2:AN is the sum range. B2:B is the reference range that contains Opportunity ID. A2:A25 is the range of Opportunity IDs in Data Sheet 2 that should be left out of the sum product. There are other criteria in the formula that work fine without this clause, so for the sake of this post, I am only including the clause that is causing trouble.

Formula: =SUMIFS('Data Sheet 1'!AN2:AN, 'Data Sheet 1'!$B2:B, "<>"&A2:A25, [Argument 2], [Argument 3], etc.)

Welcome to the Forum kearnetp!

Problems are not always caused where you think they are. I recommend including the entire formula.

It is not possible for us to test your formula without having some sample of your data. Sometimes problems are in the formula, and sometimes they are in the data. I recommend that you attach the file you are using with any private data removed or anonymized.

Part of the problem may be

"<>"&A2:A25

SUMIFS does not expect an array there, only an expression that can be evaluated for a single value. When you do this, Excel versions before 2021 will just use the value in the first cell of the range. Later versions will return an array. To confirm what this does for you I would want to try it with the full formula, using your data.

What version of Excel are you using? Excel 2016 is also known as v16, but 16.72 is not meaningful to me. I use Microsoft 365, and the build number is version 2302. We are looking for something like MS 365, Excel 2019, etc.

Hello! Thanks for getting back to me here. Apologies, I am using MS 365. I should also note that this formula is being used in Google Sheets as well, so not sure if this would affect anything.

Let me include a sample workbook with the data - it will have three tabs, one with the full data set, the second with the range that is to be excluded, and the third for a space that the formula can be calculated.

Unfortunately, this data set is extremely sensitive and I cannot share all details. The original formula has other conditions that would not make sense without sharing the full files. I hope it is enough to say that if this specific condition is removed, the formula works as intended for all other conditions. At the very least, how would you approach summing the values up that do not share an Opportunity ID with the shortened list in the workbook above, without other conditions? If I have this, I can get creative and rework full formula on my own.