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
I think this is what you are looking for..  Pivot Table..

Please see attached.

Kent
Copy-of-sampleExample101-1.xlsx

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.

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.

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

ASKER

thank you