Why is my SUMIFS formula with 2 "DNE" criteria (w/in the same row) acting as an "OR" instead of an "AND?"

[carlcryptocurrency]

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Tx[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Product[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Price[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Fees[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Total[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Purchase[/TD]
[TD]Widget[/TD]
[TD="align: right"]$500.00[/TD]
[TD="align: right"]$55.00[/TD]
[TD="align: right"]$555.00[/TD]
[/TR]
[TR]
[TD]Rob[/TD]
[TD]Credit[/TD]
[TD]Flibbity Jibbet[/TD]
[TD="align: right"]$400.00[/TD]
[TD="align: right"]$1.00[/TD]
[TD="align: right"]$401.00[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]Credit[/TD]
[TD]Clown[/TD]
[TD="align: right"]$300.00[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"]$305.00[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Purchase[/TD]
[TD]Poppins[/TD]
[TD="align: right"]$250.00[/TD]
[TD="align: right"]$30.00[/TD]
[TD="align: right"]$280.00[/TD]
[/TR]
[TR]
[TD]Karen[/TD]
[TD]Debit[/TD]
[TD]Widget[/TD]
[TD="align: right"]$125.00[/TD]
[TD="align: right"]$8.00[/TD]
[TD="align: right"]$133.00[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Purchase[/TD]
[TD]Flibbity Jibbet[/TD]
[TD="align: right"]$10.00[/TD]
[TD="align: right"]$15.00[/TD]
[TD="align: right"]$25.00[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Credit[/TD]
[TD]Clown[/TD]
[TD="align: right"]$775.00[/TD]
[TD="align: right"]$10.00[/TD]
[TD="align: right"]$785.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5, align: right"]"=SUMIFS(F2:F8,A2:A8,"<>John",B2:B8,"<>Purchase")"is omitting rows 2, 5, 7, & 8 for a total of[/TD]
[TD="align: right"]$839.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Tx[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Product[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Price[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Fees[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Total[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Purchase[/TD]
[TD]Widget[/TD]
[TD="align: right"]$500.00[/TD]
[TD="align: right"]$55.00[/TD]
[TD="align: right"]$555.00[/TD]
[/TR]
[TR]
[TD]Rob[/TD]
[TD]Credit[/TD]
[TD]Flibbity Jibbet[/TD]
[TD="align: right"]$400.00[/TD]
[TD="align: right"]$1.00[/TD]
[TD="align: right"]$401.00[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]Credit[/TD]
[TD]Clown[/TD]
[TD="align: right"]$300.00[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"]$305.00[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Purchase[/TD]
[TD]Poppins[/TD]
[TD="align: right"]$250.00[/TD]
[TD="align: right"]$30.00[/TD]
[TD="align: right"]$280.00[/TD]
[/TR]
[TR]
[TD]Karen[/TD]
[TD]Debit[/TD]
[TD]Widget[/TD]
[TD="align: right"]$125.00[/TD]
[TD="align: right"]$8.00[/TD]
[TD="align: right"]$133.00[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Purchase[/TD]
[TD]Flibbity Jibbet[/TD]
[TD="align: right"]$10.00[/TD]
[TD="align: right"]$15.00[/TD]
[TD="align: right"]$25.00[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Credit[/TD]
[TD]Clown[/TD]
[TD="align: right"]$775.00[/TD]
[TD="align: right"]$10.00[/TD]
[TD="align: right"]$785.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5, align: right"]What do we do so it only omits rows 2 & 7 for an accurate total of[/TD]
[TD="align: right"]$1,904[/TD]
[/TR]
</tbody>[/TABLE]

 

[inactiveuserps07]

Carl, your SUMIFS is essentially saying this (in words):

"Sum up all values from column F from among the subset that first eliminates every instance of 'John' and then further eliminates 'Purchase' from the remaining options."

And that's not what you want.

Give this a try:

=SUMPRODUCT((A2:A8&B2:B8 <> "JohnPurchase")*(F2:F8))

 

[Aladin Akyurek]

Your intent is:

=SUM(F2:F8)-SUMIFS(F2:F8,A2:A8,"john",B2:B8,"purchase")

where the SUMIFS bit indeed runs as it were, an and.