LinkBack URL
About LinkBacks
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
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
hi Joseph, hope you are doing well too. here's the formula for L23:
Formula:
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:
Formula: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&"*")
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Use this formula for the L23
which will give you the answer of 179.
Please Login or Register to view this content.
Here I am rounding down to 0 digit.
________________________________________________________________________________
if you feel some one has helped please add to reputation
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 ?
Hi Benishriyo, I am attaching the file again.
sorry about that. forgot about your version. SUMIFS only available in Excel 2007 & above. try this:
Formula:Please Login or Register to view this content.
or this array formula. not sure which would be faster for you.
Formula: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:
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))
This is excellent. Hats off to you benishriyo. I wish you I had your skills to build formula up this quick.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks