# Alternatives for Sumproduct?

1. ## 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_amount)
-SUMPRODUCTO(--(range_accountnumbers=6230003);--(range_dates<=combobox_end_month);range_credit_amount)

'Value before begin month (= debit - credit)
- N("valor serv_prof antes mes 1")
- (
SUMPRODUCT(--(range_accountnumbers=6230003);--(range_dates<=combobox_begin_month);range_debet_amount)
-SUMPRODUCTO(--(range_accountnumbers=6230003);--(range_dates<=combobox_begin_month);range_credit_amount)
)

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  Register To Reply

2. ## Re: 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" <MartinLos@discussions.microsoft.com> skrev i melding
news:AB3ED628-ECF3-473A-900D-94474BD6600F@microsoft.com...
>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_amount)
> -SUMPRODUCTO(--(range_accountnumbers=6230003);--(range_dates<=combobox_end_month);range_credit_amount)
>
> 'Value before begin month (= debit - credit)
> - N("valor serv_prof antes mes 1")
> - (
> SUMPRODUCT(--(range_accountnumbers=6230003);--(range_dates<=combobox_begin_month);range_debet_amount)
> -SUMPRODUCTO(--(range_accountnumbers=6230003);--(range_dates<=combobox_begin_month);range_credit_amount)
> )
>
> 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  Register To Reply