Avatar of Rayne
Rayne
Flag for United States of America asked on

Excel Formula Help Part 2

This is in reference to the preiovus question:
https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28084168.html

Thanks to Expert for helping out.

There is one more layer of complexity. Now each type have a group. I only want Grp1 numbers, not interested in other group numbers. Please note that the entire table is also a named range (myEntireRange) so ….if index(,,) is needed, that could be used to achieve what I am looking for …
How can that be done? So that it only grabs numbers that pertain to Group 1 only…

thanks
Copy-of-sampleExample101-1.xlsx
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Rayne

8/22/2022 - Mon
byundt

For the top example, you might consider:
=SUMPRODUCT((INDEX(myEntireRange,,1)=Group)*(INDEX(myEntireRange,,2)={"type1","type7"})*INDEX(myEntireRange,,MATCH(mode,$F$10:$J$10,0)))
I couldn't use the named range types because you had commas separated values in it.

For the bottom example, you might consider:
=SUMPRODUCT((INDEX(myEntireRange,,1)=Group)*INDEX(myEntireRange,,MATCH(C22,$F$10:$J$10,0)))
sampleExample101-1Q28084271.xlsx
Kent Dyer

I think this is what you are looking for..  Pivot Table..

Please see attached.

Kent
Copy-of-sampleExample101-1.xlsx
Rayne

ASKER
Brian,
Thanks for the suggestion. Like in the older question – I was able to change the values of [types] and it will calculate to for that combination. You suggested two ways – but can there be just one formula that does it, so that as you change values of Types, it also calculates…just like it did in the previous question…
Kent - Pivot is good but I don’t prefer it for this case.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Rayne

ASKER
instead of Hardcoing vlaues in the formulas, if the formulas can read the cell values, that would be preffered
ASKER CERTIFIED SOLUTION
byundt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Rayne

ASKER
Brian,

If you had to do this in VBA, what way you would do it to make it the fastest - the most efficient  and fast way to calculate via VBA
Rayne

ASKER
thank you
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rayne

ASKER