Sumifs & Not Like

[Cow Girl]

I have a large spreadsheet that looks something like this:


Dest. Origin Weight Carrier
NYC PIT 37,133 FedEx
LA OBX 5,280 DHL
NYC OBX 35,800 FedEx
CHI OBX 28,100 UPS
NYC PIT 39,680 DHL
TOR OBX 54,648 BAX



I need to summarize this various ways. One of them is I need the total
weight from origin OBX, but not using the carrier UPS. So, my answer should
be 95,728.


The problem is there are way more carriers than just the 3 that I show, and
I only need to exclude 1 carrier.

So, how Can I use write a formula that says sum if the origin is "OBX" and
the carrier NOT "UPS"? I use the sumifs formula (I'm using Excel 2007) all
the time. But, this is the first time I need to exclude something.

Any ideas? All and all help is greatly appreciated!!!

 

[Mike H]

Hi,

You could try this

=SUMPRODUCT((B2:B20="OBX")*(D220<>"UPS")*(C2:C20))

Mike

 

[Cow Girl]

Thank you!! That worked perfectly. One more question, please? Is there a way
to count vs. summing?

 

[Mike H]

I assume you mean count the number of entries summed by the previous formula

=SUMPRODUCT((B2:B20="OBX")*(D220<>"UPS")*(C2:C20<>""))

Mike

 

[Cow Girl]

Yes, that is what I mean. I'm looking for an answer of 3. When I do that,
though, it comes up as an error: #N/A

Any ideas what I might be doing wrong?

 

[Cow Girl]

Mike, I figured it out. I did not specify the exact range for the carriers.
That is, I did D instead of D220. It doesn't work if you do that. Thank
you so much for all of your help!! Cathy

 

[Mike H]

Glad I could help and on the voyage of self discovery you just found out that
sumproduct doesn't work on full columns in your version of Excel. I bet you
never forget that.

Mike