Totalling non adjacent cells in a spreadsheet

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
 
R

RagDyeR

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
 
R

RagDyeR

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
 
R

RagDyeR

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
 
B

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!
 
R

RagDyeR

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

substract cells from other cells then forwarding to a different ce 1
trying to create a link to a link in Excel (2007) 3
SUM cells in every other column IF adjacent cell equals a criteria??? 1
Maintaining formula consistancy when copying formulas 4
Linking Formulae Across a Workbook 2
directing the sum of a cell to either of two cells depending on va 3
Use data fed in adjacent cells to sort and copy the data to spec named range 0
Conditional Formatting in Excel 2007 1

Top