# Formula required for return a section wise different objects number of quantities.

1. ## Formula required for return a section wise different objects number of quantities.

Hi,

Required a formula help for return a section wise different objects number of quantities in Cells K5:P5,which are existing in each Lot, as based on data set available in Column A:F,and also required in Cells k7:P7 joint lookup items quantities values as per section wise.

Sample data sheet enclosed.

Thanks for the help.  Register To Reply

2. ## Re: Formula required for return a section wise different objects number of quantities.

In K5, copied across:
=INDEX((\$B\$3:\$F\$7,\$B\$11:\$F\$15,\$B\$19:\$F\$23),MATCH(\$J5,\$A\$3:\$A\$7,0),MATCH(K\$4,\$B\$2:\$F\$2,0),1+INT((COLUMNS(\$A:A)-1)/2))

What answers do you expect in the cells beginning K7?  Register To Reply

3. ## Re: Formula required for return a section wise different objects number of quantities.

below sample data sheet enclosed with desired answers.  Register To Reply

4. ## Re: Formula required for return a section wise different objects number of quantities.

"QWS ASH" means sum all rows those have "QWS" or "ASH" (row 6 & 7)?
You expect it to be "POL QWS", row 5+row 7, why?
Assum it was typo.
With my logic, try in K5: ``Please Login or Register  to view this content.``  Register To Reply

5. ## Re: Formula required for return a section wise different objects number of quantities.

Many thanks bebo for your solution,

I am actually looking answers for "QWS ASH" sum all rows (row 6 & 7).

It was typing error for expecting answers to (row 5 & 7).

Thanks again,  Register To Reply

6. ## Re: Formula required for return a section wise different objects number of quantities.

If Lot names are the same for each section

K7
=SUM(SUMIFS(INDEX((\$B\$3:\$F\$7,\$B\$11:\$F\$15,\$B\$19:\$F\$23),,MATCH(K6,\$B\$2:\$F\$2,),RIGHT(LOOKUP("z",\$K\$3:K\$3))),\$A\$3:\$A\$7,MID(\$J7,{1;5},3)))

If not
=SUMPRODUCT((LOOKUP(ROW(\$A\$1:\$A\$23),ROW(\$A\$1:\$A\$23)/(LEFT(\$A\$1:\$A\$23,4)="Sect"),\$A\$1:\$A\$23)=LOOKUP("z",\$K\$3:K\$3))*ISNUMBER(MATCH(\$A\$1:\$A\$23,MID(\$J7,{1;5},3),)),INDEX(\$B\$1:\$F\$23,,MATCH(K6,\$B\$2:\$F\$2,)))  Register To Reply

7. ## Re: Formula required for return a section wise different objects number of quantities.

K7=SUMPRODUCT(\$A\$1:\$F\$23,(INDEX(\$A\$1:\$F\$23,MATCH(LOOKUP("zzzz",\$K\$3:K\$3),\$A\$1:\$A\$23,0)+1,)=K6)*ISNUMBER(SEARCH(\$A\$1:\$A\$23,\$J7))*(LOOKUP(ROW(\$A\$1:\$A\$23),ROW(\$A\$1:\$A\$23)/ISNUMBER(SEARCH("Section-",\$A\$1:\$A\$23)),\$A\$1:\$A\$23)=LOOKUP("zzzz",\$K\$3:K\$3)))
Formula:  `Please Login or Register  to view this content.`

Try the above, copy and paste towards right  Register To Reply

8. ## Re: Formula required for return a section wise different objects number of quantities.

Thanks to all of you for your great formula solutions.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 