I hope I can explain this correctly. I need to lookup one value and return multiple values which then in turn sum values from a different schedule. I have tried index and match / sumifs without much luck. I have attached example to explain better.
TIA for any assistance!
Malise
hi Malise, welcome to the forum. it's a little complex so i'm not sure if you know how to fit it to your actual situation. try this array formula in B2 of Totals sheet:
=SUM((INDEX(Co!$B$2:$D$5,,MATCH(VLOOKUP(A2,State!$A$1:$B$3,2,0),Co!$B$1:$D$1,0))="X")*('Mod Amts'!$B$2:$E$5)*(TRANSPOSE(INDEX(Mods!$B$2:$D$5,,MATCH(A2,Mods!$B$1:$D$1,0)))="X"))
...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again. drag down the formula
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
WOW! PERFECT! I will figure out for actual model but I have been beating my head against the wall on this one.
Thanks very much for your time and expertise! It is greatly appreciated!
I was able to convert to actual schedule easily. I forgot one other item that changes the calculation. I tried to modify without any success. In some cases, no combos are required so it would just be the mods, state criteria of which to add. I have attached revised test model.
TIA for any assistance!
Regards,
Malise
Revised file with more detail.
there should be a shorter way, but you can try this use this long formula for now in C2:
Formula:
Please Login or Register to view this content.
Perfect! Thanks TONS!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks