Help with SUMIFS with Multiple Criteria in and out of Same Column

[Channielynn]

I need a SUMIF or SUMIFS formula to pull the total I am needing from the example order below. Every order has at least one of 4 document and/or freight charges when it feeds onto the spreadsheet. What I need is a way to extract the amounts without these charges. All of the data in question is remaining on the same sheet.
Additional information, the issue I have having is with the total for Branch/Plant ABC. I need the total extended price from that column without the last 4 rows showing in the totals (INTL, HPP, etc).

What I have tried –

=SUMIFS(F:F,H:H,”ABC”,A:A,{“ <>HPP”,”<>INTL”,…})

The problem is it is not removing all of these, one or more are still feeding into my total.

Item Number	Quantity Shipped	UofM	Description 1	Unit Price	Extended Price	Extended Weight	Branch / Plant	Lot Number
123	200	BG	PROD 1	5.00	1000.00	10,000.00	ABC	01JAN05A1
456	150	EA	PROD 2	5.00	750.00	750.00	ABC	01JAN05A2
789	22	BG	PROD 3	5.00	110.00	1,100.00	DEF	01JAN05A3
147	36	BG	PROD 4	5.00	180.00	1,800.00	DEF	01JAN05A4
258	70	CS	PROD 5	5.00	350.00	840.00	ABC	01JAN05A5
369	58	BG	PROD 6	5.00	290.00	2,900.00	GHI	01JAN05A6
987-12	118	BG	PROD 7	5.00	590.00	5,900.00	GHI	01JAN05A7
654-23	5	KN	PROD 8	5.00	25.00	25.00	ABC	01JAN05A8
258-74	23	KN	PROD 9	5.00	115.00	115.00	ABC	01JAN05A9
HPP	1	EA	HEALTH DOCUMENTS	2,000.00	2000.00		ABC
INTL	1	EA	INTERNATIONAL DOCUMENTS	60.00	60.00		ABC
ASSAY-PROF	1	EA	ASSAY PROFILE	1,000.00	1000.00		ABC
FRT	1	EA	FREIGHT CHARGE	100.00	100.00		ABC

<tbody>
</tbody><colgroup><col span="3"><col><col span="4"><col></colgroup>

 

[Jonmo1]

You can't do a criteria of multiple NOT Equal in Sumifs like that.

You'd have to create a seperate criteria for each
=SUMIFS(F:F,H:H,"ABC",A:A,"<>HPP",A:A,"<>INTL")

If there's alot of those NOT equal criteria, I'd suggest sumproduct
=SUMPRODUCT(--(H1:H100="ABC"),--(ISERROR(MATCH(A1:A100,{"HPP","INTL"},0))),F1:F100)


I highly recommend NOT using entire column references like H:H in sumproduct.

 

[Channielynn]

I appologize, let me clarify, I use the range of cells not rows, I was keeping my example simple so it didn't look cluttered, so sorry for the confusion.

There 4 NOT equal criteria (all 4 are the last 4 rows above), so I would assume I would just add the other two in the example above as ,{"HPP","INTL","ASSAY-PROF","FRT"},

Also, the -- you use, I keep those in there so in my actual spreadsheet I have...

=SUMPRODUCT(--(H37:H236="ABC"),--(ISERROR(MATCH(A37:A236,{"HPP","INTL","FRT","ASSAY-PROF"},0))),F37:F236)

However, now I am getting zero, but I am not getting any errors.

 

[Jonmo1]

My guess would be the numbers in F37:F236 are not really numbers, maybe they're 'numbers stored as text'

Does this give a result?
=SUMPRODUCT(--(H37:H236="ABC"),--(ISERROR(MATCH(A37:A236,{"HPP","INTL","FRT","ASSAY-PROF"},0))),--(F37:F236))

 

[Channielynn]

I got it to work with the first formula you gave me, switching the rows for cells and adding the two remaining paper lines... thank you so much for the help, much appreciated

Chandra

 

[Jonmo1]

Glad to help, thanks for the feedback.