Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternatives for Sumproduct?
I have 5 sheets each with data (general expenses, wages, social security,
external services and incomes). Each sheet has the same headings (accountnumber, date, descripcion of income/expenditure, debet amount, credit amount). In a seperate worksheet I want to present the summary of all costs and incomes. I have two comboboxes (1st select begin, 2nd selects end month of presentation). I use sumproduct formula, for example to calculate the total expenditures of accountnumber 6230003 I use: = 'Value of expenditures before end month (= debet-credit SUMPRODUCT(--(range_accountnumbers=6230003);--(range_dates<=combobox_end_month);range_debet_amou nt -SUMPRODUCTO(--(range_accountnumbers=6230003);--(range_dates<=combobox_end_month);range_credit_amo unt) 'Value before begin month (= debit - credit) - N("valor serv_prof antes mes 1") - SUMPRODUCT(--(range_accountnumbers=6230003);--(range_dates<=combobox_begin_month);range_debet_am ount -SUMPRODUCTO(--(range_accountnumbers=6230003);--(range_dates<=combobox_begin_month);range_credit_a mount) ) This way of working cost a lot of resources from the computer. The use of DSUM formula is dificult, since I have more than 30 accountnumbers, and I cannot change conditions that easily. Any alternative way of calculating what I want? Any ideas appreciated! TIA Martin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternatives for Sumproduct?
Hi Martin
Get a coffee, disconnect the phone and spend 30 minutes learning Pivot Tables. After that you can't imagine how you ever managed without. See http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://office.microsoft.com/en-us/as...346321033.aspx HTH. Best wishes Harald "Martin Los" skrev i melding ... I have 5 sheets each with data (general expenses, wages, social security, external services and incomes). Each sheet has the same headings (accountnumber, date, descripcion of income/expenditure, debet amount, credit amount). In a seperate worksheet I want to present the summary of all costs and incomes. I have two comboboxes (1st select begin, 2nd selects end month of presentation). I use sumproduct formula, for example to calculate the total expenditures of accountnumber 6230003 I use: = 'Value of expenditures before end month (= debet-credit) SUMPRODUCT(--(range_accountnumbers=6230003);--(range_dates<=combobox_end_month);range_debet_amou nt) -SUMPRODUCTO(--(range_accountnumbers=6230003);--(range_dates<=combobox_end_month);range_credit_amo unt) 'Value before begin month (= debit - credit) - N("valor serv_prof antes mes 1") - ( SUMPRODUCT(--(range_accountnumbers=6230003);--(range_dates<=combobox_begin_month);range_debet_am ount) -SUMPRODUCTO(--(range_accountnumbers=6230003);--(range_dates<=combobox_begin_month);range_credit_a mount) ) This way of working cost a lot of resources from the computer. The use of DSUM formula is dificult, since I have more than 30 accountnumbers, and I cannot change conditions that easily. Any alternative way of calculating what I want? Any ideas appreciated! TIA Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking at alternatives | Excel Discussion (Misc queries) | |||
Indirect alternatives | Excel Worksheet Functions | |||
What are the alternatives ??? | Excel Discussion (Misc queries) | |||
Sumproduct doesn't work with columns... alternatives? | Excel Worksheet Functions | |||
Alternatives to Excel | Excel Discussion (Misc queries) |