|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 04:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com