Multiplying cells and sum then for a range

0 votes

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.

Oct 1, 2022 in Others by Kithuzzz
• 38,000 points
1,032 views

1 answer to this question.

0 votes

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))
answered Oct 2, 2022 by narikkadan
• 63,640 points

Related Questions In Others

0 votes
1 answer
0 votes
2 answers

Is Peterson’s Algorithm a good solution for Critical Section problem? If yes, then why? If no, then why?

Peterson’s solution provides a good algorithmic description ...READ MORE

answered Jul 6, 2020 in Others by Gitika
• 65,850 points
2,231 views
0 votes
2 answers
0 votes
1 answer

Excel COUNTIF formula

Please see MS Excel: COUNTIF Function (WS) You should ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
• 63,640 points
772 views
0 votes
1 answer

Is there a maximum number of formula fields allowed in Excel (2010)

See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx for limits on specs it doesn't indicate ...READ MORE

answered Sep 30, 2022 in Others by narikkadan
• 63,640 points
816 views
0 votes
1 answer

INDEX formula in Excel, Top 10, repeats previous value

Try this formula in cell W4: =IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX($I$2:$I$26,MATCH(V4,$R$2:$R$26,0))) The calculation ...READ MORE

answered Oct 8, 2022 in Others by narikkadan
• 63,640 points
963 views
0 votes
1 answer

Repeated Multiplication formula for Excel

Fun little problem using some basic maths: =A1*3^ROW(A1) READ MORE

answered Oct 8, 2022 in Others by narikkadan
• 63,640 points
1,005 views
0 votes
1 answer
0 votes
1 answer

VBA Loop to select then copy a range of cells based on value in column B

Try this: Sub Macro2() Dim ...READ MORE

answered Mar 23, 2023 in Others by narikkadan
• 63,640 points
2,278 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP