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>

Name	Tx	Product	Price	Fees	Total
John	Purchase	Widget	$500.00	$55.00	$555.00
Rob	Credit	Flibbity Jibbet	$400.00	$1.00	$401.00
George	Credit	Clown	$300.00	$5.00	$305.00
Mary	Purchase	Poppins	$250.00	$30.00	$280.00
Karen	Debit	Widget	$125.00	$8.00	$133.00
John	Purchase	Flibbity Jibbet	$10.00	$15.00	$25.00
John	Credit	Clown	$775.00	$10.00	$785.00
"=SUMIFS(F2:F8,A2:A8,"<>John",B2:B8,"<>Purchase")"is omitting rows 2, 5, 7, & 8 for a total of	$839.00
Name	Tx	Product	Price	Fees	Total
John	Purchase	Widget	$500.00	$55.00	$555.00
Rob	Credit	Flibbity Jibbet	$400.00	$1.00	$401.00
George	Credit	Clown	$300.00	$5.00	$305.00
Mary	Purchase	Poppins	$250.00	$30.00	$280.00
Karen	Debit	Widget	$125.00	$8.00	$133.00
John	Purchase	Flibbity Jibbet	$10.00	$15.00	$25.00
John	Credit	Clown	$775.00	$10.00	$785.00
What do we do so it only omits rows 2 & 7 for an accurate total of	$1,904

<tbody>
</tbody>

 

[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.