sumif countif

[sabrinigreen]

Hello,
I am working with a sheet of freight data.

I need to know a few things -
How many times we used a carrier, how much weight was shipped and
how much money we paid that carrier.

furthermore, I then to need to separate by inbound or outbound (mode) and total it.

My data is:
Column K Column L Column M Column N
CARRIER NAME AMT $PAID SHIP WGT. MODE
AMERICAN 900.00 6100 O

The rows run until 1400.

I used =SUMIF(K1:K1500,"AMERICAN",L1:L1500) in Column O to get the total amount paid by carrier (I put 1500 to be safe to include all)

So do I have to manually type in each carrier for this?

Thank you

[TMS]

Probably easiest to use a Pivot Table

Regards

[tlafferty]

Have you considered using a pivot table? Quick, easy, and very customizable. Click anywhere in your data, click insert pivot table, choose your data range (by default it will grab the current region of whatever active cell you chose), choose a location, add fields, choose which fields you want to total, and voila - you're done!

[scottylad2]

Upload a sample if you can, it will be easier but yeas for the Sumif you will need a different name input for each carrier

[sabrinigreen]

Not sure how to upload something here. But I will try a pivot table. Never used it before. Let's see how this goes.

CARRIER NAME AMT $PAID SHIP WGT. LBS. MODE
AMERICAN TRANSPORT GROUP, LLC 375.00 1000 T
AVALON EXPRESS INC. 220.77 1000 O
AVALON EXPRESS INC. 224.78 1000 O
C.H. ROBINSON CO 150.00 1000 O
J & L 150.00 1000 O

[sabrinigreen]

Holy Cow, the pivot table is awesome!!!!!
Thank you!

[sabrinigreen]

Okay, what the pivot table doesn't do, though, is tell me how many times we used each carrier.

And can I divide it by date, for example, adding July - September only?

[sabrinigreen]

Cool, what the pivot table doesn't do is tell us how many time we used each carrier and I also need to know how many times we used each carrier outbound and inbound (mode).

Thanks!

[tlafferty]

use the date as a report filter. Drag the carrier into the sum area and it will give a count. Make sure to leave the carrier in the row label area for flagging your data. You could add row labels for whatever field tracks outbound/inbound, and drag that into the sum area as well. If that's not too clear, try uploading a sample book and one of use will play with it.

[TMS]

You're welcome. Thanks for the rep.

By default, a Pivot Table sums numeric values but I'm pretty sure you can also get it to count them.

Pivot Tables and their capabilities are not a strength of mine but you should be able to do it.

Post a sample workbook if you need more assistance.

Regards

[sabrinigreen]

Okay, I added a sample database to my first post - I can't seem to 'drag' the carrier to the sum area.

[sabrinigreen]

Okay, I figured out how to drag the carrier to the sum box.
I figured out how to show only inbound or outbound or both.
Now is there anyway to get it in the same row? Or if I do it separately, like here only using outbound, can I get rid of this duplicate row?

[TMS]

See attached