adding column a if column b = X

  • Thread starter Thread starter Shelly
  • Start date Start date
S

Shelly

Basically, I am trying to take financial figures from
three spread sheets and to put them into another sheet
that is a finacial report.

I have a file with three spread sheets, each sheet is a
different account. Each row is a transaction, like this:
A B
$505 rent
$45 fees
$45 fees
$720 travel
$505 rent
$600 travel


I've added a fourth sheet, a master report sheet. In my
master sheet, I'd like to have a column for each sheet
and rows for each category that have a formula to pull
the data from the other sheets to list the totals like
this example:
1) sheet x, 2) sheet y, 3) sheet z.
rent 1010
fees 90
travel 1320

So all of the items in sheet x, that have rent in column
b, would have the value in column a summed into the
master sheet cell.

This is a bit beyond me.

Thanks for any help you can offer.
 
Hi Shelly

assuming that in your master report column A stores the transaction
types you can enter the follwoing formula for sheet x (according to
your example in B2, in the type 'rent' in A2)
=SUMIF('sheet x'!$B$1:$B$9999,$A2,'sheet x'!$A$1:$A$9999)
copy this formula down for all required rows.
For the other columns you have to change the sheet name manually

Frank
 
Sorry - I am a bit slow on formulas but I don't think
Frank understood my question - or maybe I don't
understand the answer!

(All sheets are in one workbook.)
I want cell A3 in the master sheet to look at the x sheet.
In x sheet, if any cells in the range b1 to b6 contain
the value "rent", the value next to it in column a should
be added with others that have "rent" in column be. The
sum should appear in cell A3 of the master.

So, if this is the x sheet:
x sheet
A B
$505 rent
$45 fees
$45 fees
$720 travel
$505 rent
$600 travel

The master sheet cell A3 value returned is 1010 (total of
rent costs.)

What formula would I type in master sheet, cell A3 to
tell if to look at x sheet, and to add the number in
column a if column b contains the word rent.

(Actually column b has another number, 23 to indicate
rent but I thought that it would be easier to understand
the answer if a word is used...)

Thanks.
 
Back
Top