Excel Magic Trick 1010: SUMPRODUCT To Multiply Filtered Columns or Columns With Hidden Rows

See how to multiply and then add two columns with a Filter or Hidden Rows using SUBTOTAL, OFFSET and the SUMPRODUCT function:
1. OFFSET function inside the SUBTOTAL function to create the filtered column of values (rows hidden)
2. SUMPRODUCT to multiply the two filtered columns
3. OFFSET function with a function argument array operation
4. Array Constant
5. Row function to create an array of values

SUMPRODUCT on filtered columns. Multiply Filtered Columns. Multiply Columns With Criteria. Multiply Columns Conditionally.

[gexcel]

These two guys have changed the lives of many people, I hope they continue doing it for a long long time!

[excelisfun]

Yes, that is him: The Excel He is the guy that started the whole Online Excel Team so many years ago! Woo Hoo!

[excelisfun]

We will! You too, Gexcel, with all of your videos!

[daviscrt]

Watching you & Mr Excel has improved my excel knowledge so much, it has given me more job Opportunities at work. thank you

[SK-tbyu]

Awesome, now i am going to impress my Boss, thanks !!!

[excelisfun]

Great, ExcelStrategy!! I am glad that is new and helpful!

[nimrodts]

One of your best videos in my opinion.
Truly brilliant technique.
Array formulas are my weakness in excel, so this is very helpful.
Well done Mike! Thank you for all your hard work.

[johnmann]

You never cease to amaze me with the tricks you pull off with Excel! Always my go to source!

[ExcelStrategy]

That's an amazing technique ! Iv never seen this use of an array vector inside the OFFSET function !!!! Can't believe it !

[walkwe]

I kept searching for a solution and found one of your videos that offered a couple solutions, either using offset or using sum(index:index) which worked for what I needed, again thanks for your thousands of videos!

[thebreadlist]

Thank you. I watched another video about that couldn't get it to work correctly. I got it on the first try with your video. Thanks!

[excelisfun]

Thanks, krn14242!

I watch all of Mr Excel's videos! Yes I did see it. I would have done that formula much differently :). Too bad it was not a Duel!

[brosseauchristian]

Thank you so much, Mike. This formula made my day!

[znamikeanz]

Thanks for all the videos. These will def. help me in the long run.

[dandan]

Wow. Nice. I'll have to go back and try to digest this concept of "offset."

[chinneyesclara]

That's so cool. Thanks. It helps me a lot

[planiolro]

Awesome Video which typically for you.

[johnborg]

Awesome!!!! never seem to get enough of these tricks and formulas :) :)

[hammondeggsmusic]

Excellent - thank you! This explained everything I needed to do.

[stculp]

GREAT video and explanation! It made my workday 100% better 👍