MicrosoftThe use of IFS is a new idea to me that I will duly squirrel away 🙂
An option I have used to combine column ranges into a 2D range is CHOOSE. For example
= CHOOSE( {1,2,3}, critRng1, critRng2, CritRng3 )
will bring together 3 separate criterion ranges, so that
= SORT( UNIQUE( CHOOSE({1,2,3}, critRng1, critRng2, CritRng3) ), {1,2,3} )
will return the unique combinations sorted before using INDEX to split the columns apart, giving
= SUMIFS( valueRng,
critRng1, INDEX(combinedCriteria#,0,1),
critRng2, INDEX(combinedCriteria#,0,2),
CritRng3, INDEX(combinedCriteria#,0,3))
The content is a list oriented pivot table with 3 levels of row header. To develop a crosstab, one or more of the criteria ranges would need to be treated separately and transposed.
p.s. Another source of good quality DA material is Mike Girvin. I tend to simply explore options rather than exposing myself to too many blogs; that way there is the 'thrill of the chase' even if I do finish up reinventing the wheel!
