SUMIFS for Not Equal (<>)

stratman

I have a SUMIFS formula that works:

=SUMIFS({GLData Range 1}, {GLData Range 6}, (@cell = "RES602835"))

I need to add a bit that would look at another column (let's call it GLData Range X) and pull in results that don't equal "turkey".

I've tried using <> as well as NOT but can't seem to get it to work. I wind up with a result of 0 which is not correct.

Thanks.

Nic Larsen

Does it work if you remove the quotes from around the 96600?

{Fund}, <>96600

Nic Larsen

Try:

SUMIFS({GLData Range 1}, {GLData Range 6}, @cell = "RES602835", {GLData Range X}, NOT(CONTAINS("Turkey", @cell)

stratman

I see but no. Incorrect result is still showing.

Michelle Choate 2

First, as a reminder - Best practice is to always name your ranges, so that way in the future you know what you are looking at if you have to trouble shoot or reverse engineer what you were doing. Or in case your formula got deleted and you need to decommission references at the sheet level, you can do so because you know what they are exactly.

Second, have you written it like this?

=SUMIFS({GLData Range 1}, {GLData Range 6}, (@cell = "RES602835"), {GLData Range X}, <>"Turkey")

stratman

Thanks Michelle. Named the ranges as suggested and this is the result:

=SUMIFS({Amount}, {SType}, (@cell = "RES602835"), {Fund}, <>"Turkey")

However, this results in "0" which I know is not the answer. I'm probably overlooking something simple but can't determine what.

Paul Newcome

Are you able to provide some screenshots for reference? Can you provide a screenshot of a row that should be getting countd but is not?

stratman

Thanks for the response and offer to assist. Attaching two (2) snapshots—one that should not be counted and one that should be counted. The trigger is the Fund Column ("Turkey"). I'm trying to exclude those with a Fund number of "96600".

Thanks again.

Paul Newcome

And what is the exact formula you are actually using?

stratman

=SUMIFS({Amount}, {SType}, (@cell = "RES602835"), {Fund}, <>"96600")

Paul Newcome

What does this do?

=SUMIFS({Amount}, {SType}, @cell = "RES602835", {Fund}, @cell <>"96600")

stratman

Thanks Paul. However, still getting a result of "0".

Paul Newcome

How exactly is the Fund column being populated?

stratman

The entire spreadsheet is updated/populated via Data Shuttle. The data behind the Data Shuttle upload is in an MS Excel file.

Paul Newcome

If you click into a cell in the Fund column as if you are about to edit it, is there a leading apostrophe?

stratman

No. The data in the Excel sheet is also a number, not text.

Paul Newcome

Have you applied any formatting to the Fund column?