| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
How to sum cross two categories?
I have a journal file and a ledger.
The journal contains expense category, date, value and is randomly sequenced. The ledger is a rectangual array. Rows are expense categories, columns are months. The leger cell for a given expense category / month is the sum of journal entries with the given expense category and month. I have a feeling that Is hould be able to use something like DSUM to do this, but I can't figure it out. I'd appreciate suggestions! I daresay many people have done this, so a reference to a solution would be fine. I don't, of course, have money to solve the problem... or someone else would be doing it. Thanks -- Ian Rowlands |
|
#2
|
|||
|
|||
|
Hi
could you post some example data and based of this example data your expected result -- Regards Frank Kabel Frankfurt, Germany "Ian R" schrieb im Newsbeitrag ... I have a journal file and a ledger. The journal contains expense category, date, value and is randomly sequenced. The ledger is a rectangual array. Rows are expense categories, columns are months. The leger cell for a given expense category / month is the sum of journal entries with the given expense category and month. I have a feeling that Is hould be able to use something like DSUM to do this, but I can't figure it out. I'd appreciate suggestions! I daresay many people have done this, so a reference to a solution would be fine. I don't, of course, have money to solve the problem... or someone else would be doing it. Thanks -- Ian Rowlands |
|
#3
|
|||
|
|||
|
Frank, thanks for taking a look. I hope this (simplified!) example helps. I
actually have 12 months (of course) and about 30 categories. Journal Amount Month Cat 5 1 Contract 7 2 Goods 8 1 Goods 9 3 Services 12 2 Services 15 3 Contract 11 1 Contract 17 3 Goods Ledger Month= 1 2 3 Cat Contract 16 0 15 Goods 8 7 17 Services 0 12 9 "Frank Kabel" wrote: Hi could you post some example data and based of this example data your expected result -- Regards Frank Kabel Frankfurt, Germany "Ian R" schrieb im Newsbeitrag ... I have a journal file and a ledger. The journal contains expense category, date, value and is randomly sequenced. The ledger is a rectangual array. Rows are expense categories, columns are months. The leger cell for a given expense category / month is the sum of journal entries with the given expense category and month. I have a feeling that Is hould be able to use something like DSUM to do this, but I can't figure it out. I'd appreciate suggestions! I daresay many people have done this, so a reference to a solution would be fine. I don't, of course, have money to solve the problem... or someone else would be doing it. Thanks -- Ian Rowlands |
|
#4
|
|||
|
|||
|
Head directly for Pivot Tables. Do not pass go, do not collect $200.
http://peltiertech.com/Excel/Pivots/pivotstart.htm In article , "Ian R" wrote: Frank, thanks for taking a look. I hope this (simplified!) example helps. I actually have 12 months (of course) and about 30 categories. Journal Amount Month Cat 5 1 Contract 7 2 Goods 8 1 Goods 9 3 Services 12 2 Services 15 3 Contract 11 1 Contract 17 3 Goods Ledger Month= 1 2 3 Cat Contract 16 0 15 Goods 8 7 17 Services 0 12 9 "Frank Kabel" wrote: Hi could you post some example data and based of this example data your expected result -- Regards Frank Kabel Frankfurt, Germany "Ian R" schrieb im Newsbeitrag ... I have a journal file and a ledger. The journal contains expense category, date, value and is randomly sequenced. The ledger is a rectangual array. Rows are expense categories, columns are months. The leger cell for a given expense category / month is the sum of journal entries with the given expense category and month. I have a feeling that Is hould be able to use something like DSUM to do this, but I can't figure it out. I'd appreciate suggestions! I daresay many people have done this, so a reference to a solution would be fine. I don't, of course, have money to solve the problem... or someone else would be doing it. Thanks -- Ian Rowlands |
|
#5
|
|||
|
|||
|
Hi
use SUMPRODUCT. See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "Ian R" schrieb im Newsbeitrag ... Frank, thanks for taking a look. I hope this (simplified!) example helps. I actually have 12 months (of course) and about 30 categories. Journal Amount Month Cat 5 1 Contract 7 2 Goods 8 1 Goods 9 3 Services 12 2 Services 15 3 Contract 11 1 Contract 17 3 Goods Ledger Month= 1 2 3 Cat Contract 16 0 15 Goods 8 7 17 Services 0 12 9 "Frank Kabel" wrote: Hi could you post some example data and based of this example data your expected result -- Regards Frank Kabel Frankfurt, Germany "Ian R" schrieb im Newsbeitrag ... I have a journal file and a ledger. The journal contains expense category, date, value and is randomly sequenced. The ledger is a rectangual array. Rows are expense categories, columns are months. The leger cell for a given expense category / month is the sum of journal entries with the given expense category and month. I have a feeling that Is hould be able to use something like DSUM to do this, but I can't figure it out. I'd appreciate suggestions! I daresay many people have done this, so a reference to a solution would be fine. I don't, of course, have money to solve the problem... or someone else would be doing it. Thanks -- Ian Rowlands |
|
#6
|
|||
|
|||
|
Frank,
That was the perfect answer. thank you so much. "Frank Kabel" wrote: Hi use SUMPRODUCT. See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "Ian R" schrieb im Newsbeitrag ... Frank, thanks for taking a look. I hope this (simplified!) example helps. I actually have 12 months (of course) and about 30 categories. Journal Amount Month Cat 5 1 Contract 7 2 Goods 8 1 Goods 9 3 Services 12 2 Services 15 3 Contract 11 1 Contract 17 3 Goods Ledger Month= 1 2 3 Cat Contract 16 0 15 Goods 8 7 17 Services 0 12 9 "Frank Kabel" wrote: Hi could you post some example data and based of this example data your expected result -- Regards Frank Kabel Frankfurt, Germany "Ian R" schrieb im Newsbeitrag ... I have a journal file and a ledger. The journal contains expense category, date, value and is randomly sequenced. The ledger is a rectangual array. Rows are expense categories, columns are months. The leger cell for a given expense category / month is the sum of journal entries with the given expense category and month. I have a feeling that Is hould be able to use something like DSUM to do this, but I can't figure it out. I'd appreciate suggestions! I daresay many people have done this, so a reference to a solution would be fine. I don't, of course, have money to solve the problem... or someone else would be doing it. Thanks -- Ian Rowlands |
|
#7
|
|||
|
|||
|
Thanks, I would normally have used Pivot tables but that would require my
untutored helper to update the table. Frank knabel has provided a solution which is great for me. I appreciate your taking the time to assist, however. "JE McGimpsey" wrote: Head directly for Pivot Tables. Do not pass go, do not collect $200. http://peltiertech.com/Excel/Pivots/pivotstart.htm In article , "Ian R" wrote: Frank, thanks for taking a look. I hope this (simplified!) example helps. I actually have 12 months (of course) and about 30 categories. Journal Amount Month Cat 5 1 Contract 7 2 Goods 8 1 Goods 9 3 Services 12 2 Services 15 3 Contract 11 1 Contract 17 3 Goods Ledger Month= 1 2 3 Cat Contract 16 0 15 Goods 8 7 17 Services 0 12 9 "Frank Kabel" wrote: Hi could you post some example data and based of this example data your expected result -- Regards Frank Kabel Frankfurt, Germany "Ian R" schrieb im Newsbeitrag ... I have a journal file and a ledger. The journal contains expense category, date, value and is randomly sequenced. The ledger is a rectangual array. Rows are expense categories, columns are months. The leger cell for a given expense category / month is the sum of journal entries with the given expense category and month. I have a feeling that Is hould be able to use something like DSUM to do this, but I can't figure it out. I'd appreciate suggestions! I daresay many people have done this, so a reference to a solution would be fine. I don't, of course, have money to solve the problem... or someone else would be doing it. Thanks -- Ian Rowlands |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A budget where receipts are typed in under categories and added f. | Excel Discussion (Misc queries) | |||
| cross referancing | Excel Discussion (Misc queries) | |||
| How I can add categories in a pivot table? | Excel Worksheet Functions | |||
| How do I find a cross reference value | Excel Discussion (Misc queries) | |||
| Excel-value in a range of cells for two cross references | Excel Worksheet Functions | |||
Linear Mode
