CODEWITHSUNDEEP
excelrange
MrTheBard
MrTheBard

Reputation: 163

Combining multiple non contiguous ranges into one named range to clean up formula

I have a formula that I'm using which works pretty well, but I would like to clean it up.

=SUMIF(IB02R, A56, IB02P)+SUMIF(IB03R, A56, IB03P)+SUMIF(IB04R, A56, IB04P)

IB02R,IB03R,IB04R & IB02P,IB03P,IB04P are ranges that I defined in the name manager. They look at entire rows.

IB02R looks at B4-ND4 of another sheet IB02P looks at B5-ND5 of another sheet

and so on.

Here is the original formula:

=SUMIF('2014'!$A$4:$ND$4,A75,'2014'!$A$5:$ND$5)+SUMIF('2014'!$A$7:$ND$7,A75,'2014'!$A$8:$ND$8)+SUMIF('2014'!$A$10:$ND$10,A75,'2014'!$A$11:$ND$11)

I would like to simplify this to combine all R's and P's so instead of having 3 sumif statements I could just have =SUMIF(IB0234R, A56, IB0234P). With IB0234R and IB0234P being the ranges contained within IB02R and so forth.

The formula is looks for a match between a particular cell and every cell in 3 different rows. Rows 4,7, and 10.

If there is a match anywhere within those rows it sums up the corresponding values in Rows 5,8, and 11 respectively.

Both of my formulas work, but I would like to simplify for sake of readability and clarity.

Is this possible? I've tried a few different ways to no success.

Upvotes: 2

Views: 18362

Answers (2)

Rick
Rick

Reputation: 45281

Here is your formula:

=SUMPRODUCT((CHOOSE({1;2;3},'2014'!$A$4:$ND$4,'2014'!$A$7:$ND$7,'2014'!$A$10:$ND$10)=A56)*CHOOSE({1;2;3},'2014'!$A$5:$ND$5,'2014'!$A$8:$ND$8,'2014'!$A$11:$ND$11))

This will take a little bit of fancy named-range-fu to make it readable, but it's definitely doable.

First, combine the "first row" of each section together into another named range, like so:

=CHOOSE({1;2;3},IB02R,IB03R,IB04R) - We'll call this IB00R

Now do the same with the "second row":

=CHOOSE({1;2;3},IB02P,IB03P,IB04P) - We'll call this IB00P

Now the formula becomes:

=SUMPRODUCT((IB00R=A56)*IB00P)

To understand exactly how the formula is working, I suggest clicking Evaluate Formula on the Formulas tab, and stepping through it, and stepping in and out of your named ranges.

EDIT: Ok now I'm doubting myself - not sure if this is working correctly. I know it will work because I've done it before, but the formula below may not be quite right. I'll figure it out in a bit.

EDIT 2: As written, this doesn't work. However, there is a way around the problem, but I can't remember what it is. Still fiddling with it. If I can't figure it out I'll delete this answer.

EDIT 3: Working now. I forgot, in order to combine non contiguous rows you have to use CHOOSE() instead of INDEX() Sorry for the false start.

Upvotes: 4

John Bustos
John Bustos

Reputation: 19574

FYI, I ABSOLUTELY like Rick's answer a whole lot more, but I'll admit that what he did was new to me (which is why I love this site!!), so I only knew how to do this using VBA.

With VBA, the function you could use to do this would be as follows:

Function Disjoined_SumIf(CriteriaRange As Range, Criteria As Range, SumRange As Range) As Double

Dim ar As Integer
Dim cl As Integer

    For ar = 1 To CriteriaRange.Areas.Count
        For cl = 1 To CriteriaRange.Areas(ar).Cells.Count
            If CriteriaRange.Areas(ar).Cells(cl).Value = Criteria.Value Then Disjoined_SumIf = Disjoined_SumIf + SumRange.Areas(ar).Cells(cl).Value
        Next cl
    Next ar

End Function

And you would use it in your spreadsheet as =Disjoined_SumIf(IB0234R, A56, IB0234P)

This function will work based upon the following assumptions:

  1. The criteria range is split up the same way the sum range is (which in your case it is)
  2. It doesn't take into account things such as non-numeric data in your sum-range which would blow up the function

It's a quick and dirty solution that could be built upon to make it more robust, but it would work!!

So, again, I'd definitely go with Rick's solution, but I'm adding this for completeness' sake.

Upvotes: 2

Related Questions