Tracking Expenses

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mickey Mouse - 20 Dec 2006 11:14 GMT

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" <roger@technologyNOSPAM4u.co.uk>
Newsgroups: microsoft.public.excel
Sent: Wednesday, December 20, 2006 12:12 PM
Subject: Re: Tracking Expenses

> Hi
>
> One way
> =SUMIF($B:$B,"Electricity",$C:$C)
>
> Instead of putting the word Electricity in the formula, you could use a
> cell reference where you had entered the word Electricity, e.g in cell F1
> then enter in cell F2
> =SUMIF(B:B,F1,C:C)
>
> Now, if you enter other expense types in G1, H1 etc. and drag the formula
> across you will get the totals for other categories.
>
>>I have a list of expenses, some of these expenses are electricity bills.
>> I have a column A 'Date', column B "Description", next column C is
>> 'Amount' and next D column is 'Paid'.
>> There are several entries for electricity mixed in with other entries
>> such as registration, petrol, insurance etc
>>
>> I'd like excel (2002) to search column B and calculate all the entries
>> for electricity only so I can display the total. Is a macro the only way
>> to achieve this? I'm not familiar with macro's YET.
>>
>> Mickey

Reply to this Message

Roger Govier - 20 Dec 2006 12:41 GMT

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.

Signature

Regards

Roger Govier

> Thanks Roger, just what the doctor ordered, works fine.
> Even though I'm now using your formula, I'm afraid I don't understand
[quoted text clipped - 48 lines]
>>>
>>> Mickey

Reply to this Message

Mickey Mouse - 21 Dec 2006 12:18 GMT

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

> Hi Mickey
>
[quoted text clipped - 64 lines]
>>>>
>>>> Mickey

Reply to this Message

Mickey Mouse - 20 Dec 2006 12:47 GMT

> 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
[quoted text clipped - 10 lines]
>
>Problem solved, please ignore above post

Mickey

> ----- Original Message -----
> From: "Roger Govier" <roger@technologyNOSPAM4u.co.uk>
[quoted text clipped - 26 lines]
>>>
>>> Mickey

Reply to this Message