SUMPRODUCT with a dynamic array or any alternative might help

c1.jpg
c2.jpg

Dear All,

I have a Loan Amortization program which is dynamic in nature. It takes inputs from users and expands according to the inputs.
I have dragged the formula to about 400 rows below and according to the users inputs, the rows are populated and the rest are programmed to show "".

This is where the problem is. In one column, I am using a SUMPRODUCT formula which is dependent on one of the columns where the formula is dragged to 400 rows below.

Because some rows will be populated with the numeric values and the rest will show "" (blank) hence SUMPRODUCT gives 'VALUE' error.

I was just hoping is there any way where to have a dynamic array for SUMPRODUCT which goes until the last numeric value in that column it is depending on?
Or can anyone tell me any alternative?

Any SUPPORT will be highly appreciated.

Best Regards

As AliGW has stated, a sample file would help (as I suspect you can simplify your calculation, or make more efficient) however, in general terms:

=SUMPRODUCT((A1:A10="x")*(B1:B10="y")*C1:C10)

will, as you say, generate #VALUE! if any value in C1:C10 is non-numeric, e.g. "apple"

to avoid this behaviour, and mimic likes of SUMIF (and ignore non-numerics), you should endeavour (wherever feasible) to split your "boolean" arrays from your "value" arrays

e.g.: converting above to:

=SUMPRODUCT((A1:A10="x")*(B1:B10="y"),C1:C10)

note: no longer * the C1:C10 range

in this instance the above will return the aggregate of numbers (stored as numbers) - and ignore other data types (e.g. "apple")

for the above syntax to work however, the arrays must share same dimensions -- as is the case above - i.e. both arrays 10x1

another alternative (illustrated quite regularly by likes of @Bo_Ry), but with a little more overhead, would be:

=SUMPRODUCT((A1:A10="x")*(B1:B10="y")*N(+C1:D10))

and in this instance, given explicit coercion, the dimension of the arrays can differ (as illustrate - e.g. C1:D10 so 10x1 * 10x2)

Dear XLent,

Thank you so much for your quick response. I applied one of ur suggestions and my problem which I had been working on for 3 days, got solved.
God bless you.

Dear AliGW,

Apologies....next time will make sure to follow those.