Totalling non adjacent cells in a spreadsheet

  • Thread starter Thread starter baffled32
  • Start date Start date
B

baffled32

Hi all

I am running WXP with Excel 2003 edition. I wish to know if, and how to set
up a running total for various predefined categories in a spreadsheet.
I have set up my monthly expenditure with debit & credit columns, totals are
fine. I list all for say, August, enter Septembers beneath with a C/F
balance, each expenditure is given a category name i.e. Utilities,
Housekeeping etc.

Can I then select all the entries entered under say, Housekeeping, and place
them into a cell as a running total, (doesn't matter where i put these
results on the spreadsheet) other than manually entering a sum & listing each
cell which has an entry under housekeeping? This seems to be a long way
round, especially when I get near to a year ends worth of entries.

I hope that makes sense!

Regards

Baffled32
 
You say that your months are on a single sheet, one under the other.

If your categories are then in the same column, with their values in the
adjoining column, a simple Sumif() formula should work for you.

Say your categories are in Column C and their values are in Column D.

Set up a list somewhere on the sheet that lists all the categories.

Say the list is in K1 to K12.

In L1, enter this formula:

=Sumif(C$5:C$100,K1,D$5:D$100)

And copy down to L12.

This should give you a running total for each category.

You can oversize the ranges in the formula to allow for future expansion.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi all

I am running WXP with Excel 2003 edition. I wish to know if, and how to set
up a running total for various predefined categories in a spreadsheet.
I have set up my monthly expenditure with debit & credit columns, totals are
fine. I list all for say, August, enter Septembers beneath with a C/F
balance, each expenditure is given a category name i.e. Utilities,
Housekeeping etc.

Can I then select all the entries entered under say, Housekeeping, and place
them into a cell as a running total, (doesn't matter where i put these
results on the spreadsheet) other than manually entering a sum & listing
each
cell which has an entry under housekeeping? This seems to be a long way
round, especially when I get near to a year ends worth of entries.

I hope that makes sense!

Regards

Baffled32
 
Would you believe, I just read the subject line ... "non-adjacent cells"!

The Sumif() function will work on non-adjacent (adjoining) cells.

Use something like:

=Sumif(C$5:C$100,K1,H$5:H$100)


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


You say that your months are on a single sheet, one under the other.

If your categories are then in the same column, with their values in the
adjoining column, a simple Sumif() formula should work for you.

Say your categories are in Column C and their values are in Column D.

Set up a list somewhere on the sheet that lists all the categories.

Say the list is in K1 to K12.

In L1, enter this formula:

=Sumif(C$5:C$100,K1,D$5:D$100)

And copy down to L12.

This should give you a running total for each category.

You can oversize the ranges in the formula to allow for future expansion.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi all

I am running WXP with Excel 2003 edition. I wish to know if, and how to set
up a running total for various predefined categories in a spreadsheet.
I have set up my monthly expenditure with debit & credit columns, totals are
fine. I list all for say, August, enter Septembers beneath with a C/F
balance, each expenditure is given a category name i.e. Utilities,
Housekeeping etc.

Can I then select all the entries entered under say, Housekeeping, and place
them into a cell as a running total, (doesn't matter where i put these
results on the spreadsheet) other than manually entering a sum & listing
each
cell which has an entry under housekeeping? This seems to be a long way
round, especially when I get near to a year ends worth of entries.

I hope that makes sense!

Regards

Baffled32
 
You can even include a built in offset, like:

=SUMIF(C$5:C$100,K1,H$12:H$107)


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Would you believe, I just read the subject line ... "non-adjacent cells"!

The Sumif() function will work on non-adjacent (adjoining) cells.

Use something like:

=Sumif(C$5:C$100,K1,H$5:H$100)


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


You say that your months are on a single sheet, one under the other.

If your categories are then in the same column, with their values in the
adjoining column, a simple Sumif() formula should work for you.

Say your categories are in Column C and their values are in Column D.

Set up a list somewhere on the sheet that lists all the categories.

Say the list is in K1 to K12.

In L1, enter this formula:

=Sumif(C$5:C$100,K1,D$5:D$100)

And copy down to L12.

This should give you a running total for each category.

You can oversize the ranges in the formula to allow for future expansion.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi all

I am running WXP with Excel 2003 edition. I wish to know if, and how to set
up a running total for various predefined categories in a spreadsheet.
I have set up my monthly expenditure with debit & credit columns, totals are
fine. I list all for say, August, enter Septembers beneath with a C/F
balance, each expenditure is given a category name i.e. Utilities,
Housekeeping etc.

Can I then select all the entries entered under say, Housekeeping, and place
them into a cell as a running total, (doesn't matter where i put these
results on the spreadsheet) other than manually entering a sum & listing
each
cell which has an entry under housekeeping? This seems to be a long way
round, especially when I get near to a year ends worth of entries.

I hope that makes sense!

Regards

Baffled32
 
Hi RagDyer

Thanks so much for your help. Glad you noticed the 'non-adjacent' bit so
quickly!

Not 'Baffled' on this item anymore great!
 
Appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Hi RagDyer

Thanks so much for your help. Glad you noticed the 'non-adjacent' bit so
quickly!

Not 'Baffled' on this item anymore great!
 
Back
Top