Tracking Expenses

[Mickey Mouse]

Thanks Roger, just what the doctor ordered, works fine.
Even though I'm now using your formula, I'm afraid I don't understand it or
how it's doing it.
How would you modify the formula to act only on a range of cells (B1 to
B30), because B31 to B100 contains similar data which must not affect the
result in B1 to B30?
In short, I need the formula to only see the Electricity entries in range B1
to B30 disregarding the rest of the sheet.

Mickey




----- Original Message -----
From: "Roger Govier" <[email protected]>
Newsgroups: microsoft.public.excel
Sent: Wednesday, December 20, 2006 12:12 PM
Subject: Re: Tracking Expenses

 

[Roger Govier]

Hi Mickey

Sumif is capable of using whole columns as ranges, and that's what I
used in the absence of more information.
You can equally confine the range to a smaller area, but both ranges
must be of equal size.

Try
=SUMIF($B$1:$B$30,F$1,$C$1:$C$30)

What the formula is doing, is looking at all cells in the range B1 to
B30 and comparing them with the value in cell F1.
If the value is the same, then it is summing the equivalent row from
column C.

The $ sign fixes the range so it does not adjust as you copy across the
page as it is locking both column and row for the ranges to be used.

In the case of the cell F$1, it is only the row that is being locked,
not the column, so as you drag across it will change to G$1, H$1 etc.

I hope this clarifies it a little.

 

[Mickey Mouse]

Thanks Roger, just what the doctor ordered, works fine.
Even though I'm now using your formula, I'm afraid I don't understand it
or
how it's doing it.
How would you modify the formula to act only on a range of cells (B1 to
B30), because B31 to B100 contains similar data which must not affect the
result in B1 to B30?
In short, I need the formula to only see the Electricity entries in range
B1
to B30 disregarding the rest of the sheet.

Mickey

Problem solved, please ignore above post
Mickey




----- Original Message -----
From: "Roger Govier" <[email protected]>
Newsgroups: microsoft.public.excel
Sent: Wednesday, December 20, 2006 12:12 PM
Subject: Re: Tracking Expenses

 

[Mickey Mouse]

Thanks again Roger.
I don't pretend to completely understand all that but then I've printed it
and only read it three times. I'm sure a few times and it'll start to sink
in. lol

Cya and thanks again

Mickey