multiplying cells and sum then for a range

Question

In my excel template, I need to have a formula to multiply two numbers with 2 or 3 conditions, and then sum the found numbers in an area.

For example;

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

For first formula, i need to check the second range, found the cells that contain the same value as A2, then get the row number, find the value of the first range/that row cell, then divide it to its fourth range, and go on like this till AB1100 and sum all of that found numbers.

For second formula, i need to check the second range, found the cells that contain the same value as A2, then get the row number, check the third range if its the same value as A3, if it is then find the value of the first range/that row cell, then divide it to its fourth range, and go on like this till AB1100 and sum all of that found numbers.

I tried those with sumproduct, but it only sums both ranges and divides them in the end.

I can write this in Vba, but I need to store the values in the worksheets, so it will be better if I can do this in a formula.

I am open to suggestions.

Scott Craner Scott Craner · Accepted Answer · 2017-10-05T13:28:12

You want 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))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when Exiting Edit mode.

multiplying cells and sum then for a range

1 Answers