Excel Formula problem - "IF" function

[wkg]

Okay, this is hard to describe without taking a little while so pleas
bare with me. I work for an airline ticketing company and we are tryin
to combine the data from several spreadsheets onto one main spreadshee
which i stupidly volunteered to do.

There is one worksheet for each ticketer which list details of the far
but the two imporant aspects for this problem are the airline code an
the value of the fare.

Now, at the moment the airline code appears in column A and ticke
value in column D. This data is entered by each ticketer and it is no
sorted alphabetically by airline but by date. So excluding the column
that don't form a part of this equation the data looks like this.

BA $427.10
UA $330.00
CC $1,455.80
KL $195.00

There are around 80 airlines and 30 ticketers who have around 200 row
on each worksheet for each month.

What I have been trying to do is create a one page worksheet where th
totals for each ticketer by airline would appear, titled "TOTALS". S
each row is an airline and each column is a ticketer.

At the moment I use the following formula on a separate worksheet fo
each ticketer. =IF(Michelle!A2="BA",Michelle!D2,0)
In this case Michelle and searching for BA only. Now because there ar
over 200 entries on Michelles worksheet the formula is copied down t
row 300 and then totalled. This total is then referenced on my "TOTALS
worksheet in the cell that references Michelle and BA. Now becaus
there are over 80 airlines there is a column for each airline. As ther
are about 30 ticketers it makes this spreadsheet massive (16mb).

Is there some way I can put a formula directly in today my "TOTALS
worksheet to perform this function without having to have all thes
formulas. In Michelle's case and referencing BA, I need the formula t
search for any BA's in Michelle's column A, and add the totals for al
these matches from values in column D and total it in that one cell o
my "TOTALS" page. As mentioned earlier the airline codes on eac
ticketers worksheet cannot be sorted by airline and I don't to creat
any more work for the ticketers.

A friend of mine said it sounds like I need a macro but I know nothin
about Macros. If somebody knows a module to drop into VB to satisf
these requirements that's great but I would rather stick to usin
formulas as it is easier to explain to other staff members who kno
very little about Excel.

Thanks for reading my novel and i hope someone can save my bacon

 

[Jerry W. Lewis]

See Help for SUMIF

Jerry

 

[0-0 Wai Wai ^-^]

I don't think you must need a macro.

Would you mind to send me the related documents (you can amend it / hide /delete
something before you send)?

So I can understand more about the situation, and see how to sort it out.

Email: wai_wai_sir_plus[at]yahoo.com.hk

 

[tsides]

Hi,

This is something that Excel will do very easily for you (without a
Macro). You just have to know the right functions to use. It's a
good thing you've asked the question in a forum like this, because you
want the SUMIF function, not the IF function. With that, it's
extremely simple.

On your TOTALS sheet, list the ticketers in row row across the top,
starting in the second column. Then make a list of all the airline
codes in column A:
Michelle TicketerX TicketerY ...
BA
UA
CC
KL
.....

Now, in cell B@, enter the following formula
=SUMIF(Michelle!A:A,A2,Michelle!D)
and copy it down the row for all 80 airlines.

This will add up all the ticket values in all of column D on
Michelle's spreadhseet, but only if the value in column A, on the
corresponding row, is equal to the airline code in cell A2, which is
BA. But, as you notice, when you copy the formula down the column,
the A2 becomes A3, then A4, etc. So that each row on your totals
spreadsheet adds up the ticket values for each different airline.

Of course, after the last airline, you can put something like the
following formula in the bottom of Michelle's column, and you have the
total value of HER tickets sold across ALL airlines: =SUM(B2:B82)

Repeat this process for each ticketer: enter the formula in the top of
their column, and copy it down the column for all the airline codes.

If you want to get really fancy, and I do recommend this, you can
actually enter the formula once and not only have it copy down the
column and work for all airlines, but also copy it across the
ticketers and have it work for them, as well. Here's how. Set up
your TOTAL spreadsheet in the same way, but insert a row which is the
name of each person's spreadsheet.

Michelle TicketerX TicketerY ...
Michelle_Sheet X_Sheet Y_Sheet
BA
UA
CC
KL
.....

Now, in cell B3, enter the following formula:
=SUMIF(INDIRECT(B$2&"!A:A"),$A3,INDIRECT(B$2&"!D"))

Now, copy that cell and paste it down the entire column and across all
the Ticketers and you instantly have everything you need.

Notice that putting the $ in front of the A3 allowed the 3 to change
to 4, 5, 6, etc. as you pasted the formula down the column, but it
kept the A from changing to B, C, D, etc as you copied the formula
across the rows. And notice how B2 changed in the opposite manner as
you pasted to always point to the same row in the corresponding
column.

Now, feel free to "Hide" row 2, so it doesn't show up on your reports
to your boss.

Of course, if everyone's spreadsheet is named exactly the same as the
name you use at the top of their column on the TOTAL sheet, then you
don't need the hidden row 2:

Michelle TicketerX TicketerY ...
BA
UA
CC
KL
.....

=SUMIF(INDIRECT(B$1&"!A:A"),$A2,INDIRECT(B$1&"!D"))

So, you don't need to make any changes to each person's spreadsheet,
you don't need macros, and you don't end up with an enormous file.

Trevor