If Statement Help

R

RichC

I'm trying to get this to work, and I'm wondering if an If statement is
the most efficent way. Anyway.

In one row, I have various numbers corresponding to weeks, week #1,
week #2, week #3, etc. In a row right below that, I have a number
corresponding to shipment quantity.

City City 2 City 3
More Cities...
Arrival Week 1 3 1
Shipment Quantity 500 400 300

I'd like to see how many total arrivals I'll have in a certain week.
Week 1 should return 800 (500+300). Week 2 should return 400. Any
help would be greatly appreciated.
 
T

The Red Cardinal

RichC said:
I'm trying to get this to work, and I'm wondering if an If statement
is the most efficent way. Anyway.

In one row, I have various numbers corresponding to weeks, week #1,
week #2, week #3, etc. In a row right below that, I have a number
corresponding to shipment quantity.

City City 2 City 3
More Cities...
Arrival Week 1 3 1
Shipment Quantity 500 400 300

I'd like to see how many total arrivals I'll have in a certain week.
Week 1 should return 800 (500+300). Week 2 should return 400. Any
help would be greatly appreciated.


Sounds like SUMIF is your best bet...
 
T

The Red Cardinal

The said:
Sounds like SUMIF is your best bet...

Sorry, it goes like this:

=SUMIF (Range of weeks, the criteria you want (e.g. 1), the range of
shipment quantities)

e.g.

= SUMIF (A2:I2, "=1", A3:I3)

Thus for every cell in A2:I2 which equals 1, it will return the value in the
corresponding 3rd row and add them together.

Hope this helps.
 
D

Debra Dalgleish

I'd suggest rearranging the data to the following:

City ArrivalWeek Qty
Toronto 1 500
Ottawa 2 400
Vancouver 3 300
Montreal 1 400

This will allow you to enter multiple shipments per city per week, if
required, and will make reporting easier.

To see your totals, create a PivotTable:

1. Select a cell in the table
2. Choose Data>PivotTable and PivotChart report
3. Click Next, click Next
4. Click the Layout button
5. Drag the City button to the Row area
6. Drag the Arrival Week button to the Column Area
7. Drag the Qty button to the Data area
8. Click OK, click Finish
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top