# Sum if based on ranges of conditions

1. ## Sum if based on ranges of conditions

Hi all,

I need to do a SUMIF that is rather advanced compared to what I'm used to since it must compare arrays of different sizes (or match them sequentially). I've attached a workbook example.

I have an array A that consists of different types a, b, c, d, e, f.

Then an array B that contains ranges (lower and upper bounds) of unique identifiers that are connected with either a, b, c, d, e, or f (i.e. 1000 to 1099 belongs to a, 1100 to 1249 belongs to a, and 3000 to 3099 belongs to a, and so forth for other types).

Finally an array C that contains a very large data range of a given identifier and an associated value.

I need to do a sumif of the values in array C, given that the identifier is within any of the given ranges in array B that are associated with a given type.

Currently, my only solution is to split it up into several formulae parts, where I have 1 SUMIFS() for each identifier in array B that matches the type in array A. But this is unfeasible in my actual workbook and very manual.

Is there a way to do this in one formula? I've tried SUM(IF()) arrays, but that won't work as it usually does, since the arrays are not of the same size.  Register To Reply

2. ## Re: Sum if based on ranges of conditions

It would be easier to use a helper column and fill that with the appropriate code, e.g. put this formula in L4:

=VLOOKUP(J4,\$E\$4:\$G\$14,3)

and copy that down to the bottom of your data list. Then you can use this in B4:

=SUMIF(L:L,A4,K:K)

and copy that down.

Hope this helps.

Pete  Register To Reply

3. ## Re: Sum if based on ranges of conditions

Duplicated post.

Pete  Register To Reply

4. ## Re: Sum if based on ranges of conditions

Hi Pete,

Thanks for your suggestion. That would work well, given that I had the option to add that column. Unfortunately, in the data I have, I need to calculate it directly as I cannot modify the source data.  Register To Reply

5. ## Re: Sum if based on ranges of conditions Originally Posted by thomasms90 ... I need to calculate it directly as I cannot modify the source data ...
Okay, well good luck with that - I've given you a solution which is simple and robust.

Pete  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 