Sumifs formula not taking both conditions into account

[shortkut77]

Apologies if this question exists, but I can't find an exact situation like this. This is my very simple formula:

=SUMIFS(D8:D40,J8:J40,"<>Y",L8:L40,"<>Y")

I am trying to say "sum any cells in column D for which the corresponding cells in both columns J and L do not equal "Y." Or, to put it a more coherent way, "disregard any cells in both columns J and L with the value of "Y."

What I am finding is that the formula only sums if both columns are not "Y" and that when one column is "Y" and one column is not "Y" it returns 0. It's my understanding that the sumifs formula uses 'and' and not 'or,' which would lead me to think both conditions must be satisfied in order to work. If this were true it should only discount cells in D when both J and L are "Y" and not just one of them.

Please let me know if this makes sense.

Thanks in advance!

 

[jasonb75]

Your explanation of your understanding is a little confusing, your choice of words describes the correct working of the formula as being what you want.

With sumifs all (both) criteria must be met for the row to be included, if any of the criteria are not met then the row has a value of zero.

If you want to sum rows where either of the 2 criteria columns doesn't contain "Y" then you need to do them individually, however this runs the risk of rows where neither column contains "Y" being counted twice.

As you have office 365, you could try

=SUM(FILTER(D8:D40,(J8:J40<>"Y")+(L8:L40<>"Y")))

this will sum rows where at least one of the criteria columns contains something other than "Y".

 

[Joe4]

Welcome to the Board!

Do you have any empty cells in columns J or L?
Can you post a small sample of your data along with your expected output?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.