Amazing SUMIF Function with a FLOATING Criteria Range

[@IvanCortinas_ES]

Extremely good. Only within the reach of the chosen ones. Thank you Nabil!!!

[@Up4Excel]

Another really clever use of functions Nabil....you've definitely got a new fan here 👍. I've been playing around with this and note you can use INDIRECT in place of INDEX and MATCH as such =IFERROR(SUMIFS(Start[Sales],INDIRECT("Start["&XLOOKUP(L2,Prep[Value],Prep[Field])&"]"),L2). The elegant solution from your friend in your comments using FILTER looks good too. Always plenty of different options in Excel as you say!

[@Officeinstructor]

Awesome, thank you!

[@wayneedmondson1065]

Hi Nabil. Another great video tutorial! Thanks for sharing how to accomplish these clever solutions :)) Thumbs up!!

[@Officeinstructor]

Thank you so much Wayne... I love your comments (and wait for them to come)

[@nerenlakhraj5252]

A truly awesome technique ... thank you Nabil

[@Officeinstructor]

Glad you like it!

[@Luciano_mp]

Just brilliant, awesome. thank you Nabil...

[@Officeinstructor]

Thank you Luciano

[@fernandocaceres9930]

Thanks Nabil!!

[@Officeinstructor]

You're welcome

[@nadermounir8228]

Super amazing tricks thank you Nabil for this video..always enjoy watching your tutorials great learnings

[@Officeinstructor]

I also enjoy reading your comments, my friend Nader

[@ramywilliam7132]

Very neat!

[@Officeinstructor]

Thank you! Cheers!

[@ohdjrp4]

this is nice! thanks!

[@zaighamuddinfarooqui1705]

Very intelligent combination of excel functions. However, I've two queries regarding your these two videos. 1) Please accept my regrets because I'm unable to understand the logic of the use of "IF(SEQUENCE(Num+1)>Num,"Home",Item))" in earlier video. "(Num+1)>Num" will always give the "TRUE", so when and how it would select "Item" instead of "Home". 2). I am unable to fire up Power Query from the table "Start" as done by you in this video. Whenever I tried to load the "Start table" to PQ, no any option was active as all the options remained "grayed/dimmed". However, when I copied the table to a new file, there it worked. I'll be very grateful to you if you spare some of your precious time to explain/clarify the two above problems.

[@MohdSaifoulAzuan]

Thank u En Nabil....

[@Officeinstructor]

You are welcome

[@martyc5674]

Great video- I have a use for this one.. thanks :)

[@Officeinstructor]

Glad it was helful

[@davidlinton4127]

Great video! Thanks so much. Just wondering, would a modification be required if they hired a new manager named "West Johnson"?

[@Officeinstructor]

Everything updates by hitting CTRL + ALT + F5 (for the Query to update). If you want to fully automate the process you need a single line of VBA code.

[@arbazahmad7177]

Great 👍. But I have a problem if I make a drop-down list on column headers basis and then I select region header from that drop-down list and I make one more drop-down list on basis of previous selection for example if I select region from 1st drop-down list then 2nd drop-down list will show entire region data and I took north from 2nd drop-down list then I want to sum of all (north or whatever I select) in 3rd cell. Can you please help me on this.

[@Officeinstructor]

This tutorial is part 2 of the 5 in 1 drop list. If you use a different style of drop lists then everything needs to be modified

[@FRANKWHITE1996]

amazing

[@Officeinstructor]

Thank you