Complicated Look Up in an untidy file with calcualtions

[Excel Dumbo]

Hello friends,

please refer the file attached. the file is untidy. i need to look up values based on multiple conditions and insert simple calculations within look up formula.


Thanks heaps

[benishiryo]

hi Joseph, hope you are doing well too. here's the formula for L23:

Please Login or Register  to view this content.

i broke up the formula a little bit. hope that helps you. it's basically a repeat with different range to sum up. not sure if you want to ROUND or ROUNDDOWN to get 179 exactly. it would look like:

Please Login or Register  to view this content.

for M23:
=SUMIFS($I$10:$I$433,$A$10:$A$433,"*"&$K23,$B$10:$B$433,M$21&"*")

[shyamhappy]

Use this formula for the L23

Please Login or Register  to view this content.

which will give you the answer of 179.


Here I am rounding down to 0 digit.


________________________________________________________________________________

if you feel some one has helped please add to reputation

[Excel Dumbo]

Hi Benishriyo, i'm getting NAME# error, may be because i am using Excel 2003. What changes should i make to make the formula compatible to 2003 ?

[Excel Dumbo]

Hi Benishriyo, I am attaching the file again.

[benishiryo]

sorry about that. forgot about your version. SUMIFS only available in Excel 2007 & above. try this:

Please Login or Register  to view this content.

or this array formula. not sure which would be faster for you.

Please Login or Register  to view this content.

press CTRL + SHIFT + ENTER to confirm array formulas

same thing for rounding. just wrap it around the formula:

Please Login or Register  to view this content.

for M23:
=SUMPRODUCT($I$10:$I$433,ISNUMBER(SEARCH($K23,$A$10:$A$433))*(LEFT($B$10:$B$433,LEN(M$21))=M$21))

[Excel Dumbo]

This is excellent. Hats off to you benishriyo. I wish you I had your skills to build formula up this quick.