Excel Sums with Multiple Criteria

May 20, 2008 9:18 AM

Help me recreate these charts in Excel

I am trying to recreate these charts in Excel. I have all of the data, but I want to manipulate it and create similar charts.

I cannot come up with the appropriate SUMIF or DSUM formulas that allow me to do this. Basically, I need a formula that says:
1) Pick a type of ship
2) Was the ship in service in the year in question?
3) Sum the gross tonnage of all ships that meet these criteria.

The hang up is the requirement of absolute references in the SUMIF and DSUM formulas (i.e. "Year=1990). I need a formula that I can drag across multiple years.

posted by Frank Grimes to computers & internet (8 comments total)

What's your data look like? Do you have a row/column of years? If so, and if I understand, you could do "Year=A3" and drag it across/down as necessary, no?
posted by inigo2 at 9:30 AM on May 20

Can you manipulate the data into something like this? You'll have lots of repeating rows. Then you can make a pivot chart and group by years and ship type. (I'm totally making up these values because I know jackshit about shipping.)

A | B | C
ShipType | YearsInService | Tonnage

S/H Barge | 1990 | 15000
S/H Barge | 1991 | 15000
D/H Barge | 1990 | 20000

Your value in the pivot chart will be the sum of tonnage. Your legend field will be the ship type, and the axis field will be the years in service.
posted by desjardins at 10:24 AM on May 20

I'm using Excel 2007 but it shouldn't be too different for 2003. Type of ships should be rows (i.e. left-hand side data) and years should be your column headers. The numbers should indicate the count of each ship in a given year. Select the data set that you want to graph and choose "Area Chart." Note that each boat type should have its own series. To get the x-axis to look like years, choose X-axis "label" range as the column headers. That should just about do it unless I'm reading those plots incorrectly. Hope this helps!
posted by alrightokay at 10:25 AM on May 20

quick and dirty example
posted by desjardins at 10:28 AM on May 20

These are very close, but the problem I keep having is that there is a "Delivered" Column when the ship went into service and a "Disposition" Column when it was scrapped. I want to add every year that it is in service and stop adding when it is scrapped. It's not a hug data set, so it's probably easier to do manually. I was hoping Excel could make it easier.
posted by Frank Grimes at 11:12 AM on May 20

You can subtract the date fields to determine the number of years. For example:

delivered | disposed | number of years
5/1/1990 | 5/1/2007 | =YEAR(B1) - YEAR(A1)

After that I get a little fuzzy, but maybe someone can come along to tell you how to extrapolate exactly which years were between A1 and B1.
posted by desjardins at 12:48 PM on May 20

You can do this using the sumproduct command, here is a spreadsheet example with the formula in it. It checks the type, then looks at delivery year and then the dispose year. You do have to have something in the dispose column though so if a ship is still in service you can just put a "-" and it should work fine.
posted by mrpeach at 2:02 PM on May 20

There's a solution here.
posted by pompomtom at 6:10 PM on May 20