View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
Mike H Mike H is offline
external usenet poster
 
Posts: 11,501
Default Sumifs & Not Like

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

=SUMPRODUCT((B2:B20="OBX")*(D2:D20<"UPS")*(C2:C20 <""))

Mike

"Cow Girl" wrote:

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

"Mike H" wrote:

Hi,

You could try this

=SUMPRODUCT((B2:B20="OBX")*(D2:D20<"UPS")*(C2:C20 ))

Mike

"Cow Girl" wrote:

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!!!