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 in Others by Kithuzzz
• 20,660 points
68 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 by narikkadan
• 37,660 points

Related Questions In Others

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,890 points
1,244 views
0 votes
2 answers
0 votes
1 answer

How to take a screenshot of a current Activity and then share it?

For me, I captured and then shared ...READ MORE

answered Feb 8 in Others by Soham
• 9,670 points
57 views
0 votes
1 answer

Excel COUNTIF formula

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

answered Sep 29 in Others by narikkadan
• 37,660 points
62 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 in Others by narikkadan
• 37,660 points
57 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 in Others by narikkadan
• 37,660 points
62 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 in Others by narikkadan
• 37,660 points
62 views
0 votes
1 answer

Replacing H1 text with a logo image: best method for SEO and accessibility?

<h1> <a href="http://stackoverflow.com"> ...READ MORE

answered Feb 21 in Others by narikkadan
• 37,660 points
429 views
0 votes
1 answer

'Microsoft.Office.Interop.Excel.Range' does not contain a definition for 'get_Default'

You are using C# version 4, the ...READ MORE

answered Oct 16 in Others by narikkadan
• 37,660 points
186 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