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

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