Multiplying cells and sum then for a range

Question

I need the formula to multiply two integers with two or three conditions in my Excel template, and then I need it to sum the results in a specific location.

For example;

First range is; AI22:AI1100
Second range is; AB22:AB1100
Third range is; N22:N1100
Fourt range is; K22:K1100

I need to check the second range for cells that have the same value as A2 for the first formula, obtain the row number, get the value of the first range/that row cell, divide it to its fourth range, and continue in this manner until I reach AB1100 and add all of the numbers I discovered.

For the second formula, I must check the second range, locate the cells that have the same value as A2, obtain the row number, check the third range to see if it has the same value as A3 if it does, then determine the value of the first range/that row cell, divide it to its fourth range, and continue in this manner until AB1100 and add all of the discovered numbers.

I tried those with sumproduct, but it only combined the two ranges before dividing them.

Although I can do this in VBA, it would be better if I could do it in a formula because I need to store the numbers in the worksheets.

Answer 1

Use SUM() as an Array formula: 

=SUM(IFERROR((AB22:AB1100 = A2)*(AI22:AI1100)/(K22:K1100),0))

And:

=SUM(IFERROR((AB22:AB1100 = A2)*(N22:N1100 = A3)*(AI22:AI1100)/(K22:K1100),0))