asked on

Excel Formula Help Part 2

This is in reference to the preiovus question:
https://www.experts-exchange.com/questions/28084168/excel-formula-Help-please.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

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.

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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